SQL Snippets

[jonath...@gmail.com]

Let's share some little snippets of code that we find useful. No snippet too small! If you found it useful so may someone else

Retrosheet db

Extracting dates from the GAME_ID column in the GAMES table

-- extract year (date datatype)

year(substr(game_id,4,8))

-- example

select 

  year(substr(game_id,4,8)) as 'Game Year',

  games.* 

from games 

where game_id = 'LAN197604270';

-- extract date (date datatype) in YYYY-MM-DD format from GAME_ID column in GAMES table

cast(concat

  (

    substr(game_id,4,4),

    '-',substr(game_id,8,2),

    '-',substr(game_id,10,2)

  ) 

  as date)

-- example

select 

  cast(concat

    (

      substr(game_id,4,4),

      '-',substr(game_id,8,2),

      '-',substr(game_id,10,2)

    ) as date) as 'Game Date',

   games.* 

from games 

where game_id = 'LAN197604270';

[Bryan Cole]

Stumbled across this site the other day.  The book "Baseball Hacks" has a number of MySQL scripts available here: http://examples.oreilly.com/9780596009427/.  The book is a couple years old at this point, and you'll have to adjust the column names in the scripts, but I really like this.  The book itself is on Google Books, too, so you can search it for specific code snippets you have questions about.

-Bryan Cole