AmosFiveSix.com

Experience, Knowledge, Creativity

  • Increase font size
  • Default font size
  • Decrease font size
Home SQL How to Check for the Continental United States

How to Check for the Continental United States

E-mail Print PDF

This information is based on the United States Postal Service standard abbreviations for states and territories: http://www.usps.com/ncsc/lookups/usps_abbreviations.html

If your data needs to include addresses for the continental United States only, it's best to check if the address's state is included in the list of continental states, rather than checking if the state is not in the list of non-continental states.

For example, this incorrect SQL snipit allows addresses in Guam and other territories to pass through:

WHERE store.State NOT IN ('AK','HI','VI','PR')

This is the correct code to check for continental United States:

WHERE store.State IN ('AL', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY') AND store.Country = 'United States'
 

Subscribe

Subscribe by e-mail:

Site Search

Google Ads