Zip to Fips SQL Cheat

Quirolla
Quirolla Member
in Archive

Though there is option to have domo build a custom fips dataset for your instance and it may seem easy to do a seperate table for joining - it has arisen that sometimes joining does not provide all table rows to be assigned appropirate fips. Sometimes, conversely you are looking for specific counties or to bucket (numeric) fips in a particular way.  If either of those are applicable to your use case, hopefully the following sample will help. 

 

In case you need a quick zip to fips code, here is sample for use in SQL using Manhattan zips only. 

 

SELECT *, 

CASE

when `Zipcode`='10024' then '36061' 
when `Zipcode`='10016' then '36061' 
when `Zipcode`='10014' then '36061' 
when `Zipcode`='10026' then '36061' 
when `Zipcode`='10027' then '36061' 
when `Zipcode`='10004' then '36061' 
when `Zipcode`='10023' then '36061' 
when `Zipcode`='10020' then '36061' 
when `Zipcode`='10022' then '36061' 
when `Zipcode`='10017' then '36061' 
when `Zipcode`='10006' then '36061' 
when `Zipcode`='10019' then '36061' 
when `Zipcode`='10029' then '36061' 
when `Zipcode`='10007' then '36061' 
when `Zipcode`='10009' then '36061' 
when `Zipcode`='10010' then '36061' 
when `Zipcode`='10011' then '36061' 
when `Zipcode`='10045' then '36061' 
when `Zipcode`='10072' then '36061' 
when `Zipcode`='10038' then '36061' 
when `Zipcode`='10082' then '36061' 
when `Zipcode`='10080' then '36061' 
when `Zipcode`='10035' then '36061' 
when `Zipcode`='10106' then '36061' 
when `Zipcode`='10065' then '36061' 
when `Zipcode`='10112' then '36061' 
when `Zipcode`='10110' then '36061' 
when `Zipcode`='10087' then '36061' 
when `Zipcode`='10044' then '36061' 
when `Zipcode`='10055' then '36061' 
when `Zipcode`='10034' then '36061' 
when `Zipcode`='10048' then '36061' 
when `Zipcode`='10069' then '36061' 
when `Zipcode`='10105' then '36061' 
when `Zipcode`='10156' then '36061' 
when `Zipcode`='10163' then '36061' 
when `Zipcode`='10121' then '36061' 
when `Zipcode`='10160' then '36061' 
when `Zipcode`='10122' then '36061' 
when `Zipcode`='10154' then '36061' 
when `Zipcode`='10157' then '36061' 
when `Zipcode`='10166' then '36061' 
when `Zipcode`='10133' then '36061' 
when `Zipcode`='10128' then '36061' 
when `Zipcode`='10153' then '36061' 
when `Zipcode`='10124' then '36061' 
when `Zipcode`='10149' then '36061' 
when `Zipcode`='10132' then '36061' 
when `Zipcode`='10164' then '36061' 
when `Zipcode`='10119' then '36061' 
when `Zipcode`='10118' then '36061' 
when `Zipcode`='10151' then '36061' 
when `Zipcode`='10165' then '36061' 
when `Zipcode`='10271' then '36061' 
when `Zipcode`='10169' then '36061' 
when `Zipcode`='10279' then '36061' 
when `Zipcode`='10172' then '36061' 
when `Zipcode`='10177' then '36061' 
when `Zipcode`='10174' then '36061' 
when `Zipcode`='10270' then '36061' 
when `Zipcode`='10176' then '36061' 
when `Zipcode`='10259' then '36061' 
when `Zipcode`='10276' then '36061' 
when `Zipcode`='10277' then '36061' 
when `Zipcode`='10256' then '36061' 
when `Zipcode`='10171' then '36061' 
when `Zipcode`='10179' then '36061' 
when `Zipcode`='10249' then '36061' 
when `Zipcode`='10268' then '36061' 
when `Zipcode`='10272' then '36061' 
when `Zipcode`='10260' then '36061' 
when `Zipcode`='10213' then '36061' 
when `Zipcode`='10280' then '36061' 
when `Zipcode`='10286' then '36061' 
when `Zipcode`='10463' then '36061' 
when `Zipcode`='10281' then '36061' 
when `Zipcode`='10282' then '36061' 
when `Zipcode`='10039' then '36061' 
when `Zipcode`='10170' then '36061' 
when `Zipcode`='10025' then '36061' 
when `Zipcode`='10043' then '36061' 
when `Zipcode`='10108' then '36061' 
when `Zipcode`='10032' then '36061' 
when `Zipcode`='10261' then '36061' 
when `Zipcode`='10036' then '36061' 
when `Zipcode`='10003' then '36061' 
when `Zipcode`='10274' then '36061' 
when `Zipcode`='10013' then '36061' 
when `Zipcode`='10126' then '36061' 
when `Zipcode`='10030' then '36061' 
when `Zipcode`='10047' then '36061' 
when `Zipcode`='10012' then '36061' 
when `Zipcode`='10116' then '36061' 
when `Zipcode`='10265' then '36061' 
when `Zipcode`='10028' then '36061' 
when `Zipcode`='10041' then '36061' 
when `Zipcode`='10123' then '36061' 
when `Zipcode`='10273' then '36061' 
when `Zipcode`='10197' then '36061' 
when `Zipcode`='10278' then '36061' 
when `Zipcode`='10001' then '36061' 
when `Zipcode`='10125' then '36061' 
when `Zipcode`='10040' then '36061' 
when `Zipcode`='10129' then '36061' 
when `Zipcode`='10138' then '36061' 
when `Zipcode`='10155' then '36061' 
when `Zipcode`='10018' then '36061' 
when `Zipcode`='10275' then '36061' 
when `Zipcode`='10130' then '36061' 
when `Zipcode`='10033' then '36061' 
when `Zipcode`='10152' then '36061' 
when `Zipcode`='10114' then '36061' 
when `Zipcode`='10242' then '36061' 
when `Zipcode`='10158' then '36061' 
when `Zipcode`='10081' then '36061' 
when `Zipcode`='10168' then '36061' 
when `Zipcode`='10175' then '36061' 
when `Zipcode`='10021' then '36061' 
when `Zipcode`='10167' then '36061' 
when `Zipcode`='10031' then '36061' 
when `Zipcode`='10104' then '36061' 
when `Zipcode`='10109' then '36061' 
when `Zipcode`='10185' then '36061' 
when `Zipcode`='11201' then '36061' 
when `Zipcode`='10161' then '36061' 
when `Zipcode`='10199' then '36061' 
when `Zipcode`='10005' then '36061' 
when `Zipcode`='10150' then '36061' 
when `Zipcode`='10102' then '36061' 
when `Zipcode`='10269' then '36061' 
when `Zipcode`='10178' then '36061' 
when `Zipcode`='10008' then '36061' 
when `Zipcode`='10115' then '36061' 
when `Zipcode`='10173' then '36061' 
when `Zipcode`='10002' then '36061' 
when `Zipcode`='10285' then '36061' 
when `Zipcode`='10111' then '36061' 
when `Zipcode`='10113' then '36061' 
when `Zipcode`='10103' then '36061' 
when `Zipcode`='10107' then '36061' 
when `Zipcode`='10037' then '36061' 
when `Zipcode`='10292' then '36061' 
when `Zipcode`='10075' then '36061' 
when `Zipcode`='10101' then '36061' 
when `Zipcode`='10162' then '36061' 
when `Zipcode`='10117' then '36061' 
when `Zipcode`='10131' then '36061' 
when `Zipcode`='10159' then '36061' 
when `Zipcode`='10120' then '36061'


end as Fips
from transform_data_1

Zip to Fip Source:  https://data.ny.gov/Government-Finance/New-York-State-ZIP-Codes-County-FIPS-Cross-Referen/juva-r6g2/data 

 

Use excel's downfill function to quickly create your cases (and remove spaces) before pasting into sql. 

 

If your zip code is not formatted in 5 digits you may need to run step before this case:

 

SELECT  *,

LEFT(zipcod, 5) as zipcode

FROM source_table

 

 

 

Comments

  • Valiant
    Valiant Coach

    Great tips, thanks Lauren

  • rahul93
    rahul93 Contributor

    you could do

    select  *,  case when `zipcode` in (......) then '36061' end as fips from transform_data_1 

     

    Reduces the number of lines.

Categories