SQL snippets

Nice SQL code for data wrangling

1. SQL RegEx for height data

Source: @jonloyens on data.world baseball dataset

WITH Cleaned as (
  SELECT Name,
    CAST(replace(Height, "([0-9]+)\'.*", "$1") as decimal) as Feet,
    CASE 
      WHEN regex(Height, "[0-9]+\'[0-9.]+.") THEN CAST(replace(Height, "[0-9]\'([0-9.]+).", "$1") as decimal)
      ELSE 0.0 
    END as Inches
  FROM DataDotWorldBBallTeam
), JoinedAndCleaned as (
SELECT c.Name as Name, (Feet * 12 + Inches) as Inches, s.AssistsPerGame as AssistsPerGame, s.PointsPerGame as PointsPerGame
  FROM Cleaned as c
  JOIN DataDotWorldBBallStats as s ON c.Name = s.Name
)

SELECT * from JoinedAndCleaned order by Inches desc