SQL to convert geo_id2 field from Census ACS data to boroct201 from NYCDCP's bytes of the big apple--

boro.sql

--Replace county FIPS code with NYC boro code, concatenate with 6-digit tract ID

SELECT 
	*,
	(
  		(
  			CASE WHEN (substring(geo_id2,4,2) = '61')
          	THEN '1'
            WHEN (substring(geo_id2,4,2) = '05')
        	THEN '2'
        	WHEN (substring(geo_id2,4,2) = '47')
        	THEN '3'
			WHEN (substring(geo_id2,4,2) = '81')
          	THEN '4' 
          	WHEN (substring(geo_id2,4,2) = '85')
        	THEN '5'
			END
  
  		) || right(geo_id2,6)
    ) as boroct2010 
FROM nycacs_age