question

redder avatar image
redder asked

Search all fields in my table for State and Zip

I have a total of 15 fields that I want to search for State and Zip. Most are state abrev and some are full state names. Some zips are 5 digit and some are zip+4. Trying to find all or most all of these to parse out. Did not know what would be the best approach. Thx Ex: GA 30518; GA 30518-4321; GA 305184321; Mississippi 38618, etc.
sql-server-2005t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

Generally the best approach is to avoid situations like this through normalization. However, when that is not possible, you can write a script to do the search for you (though it can take a long time to run against a large table.)

I once ran into a similar issue with a denormalized table and wrote a script to handle the search for me, and then wrote it up for SQLServerCentral at https://www.sqlservercentral.com/articles/finding-values-with-numerous-columns

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This is pulling records when I search for a number but not the string value. Ex: GA for Georgia comes back with zero records.
0 Likes 0 ·
You need to tell it what wildcards you want. If you want to find rows that are GA then search for GA, but if you want to find fields that contain GA then search for %GA%
0 Likes 0 ·
JustinKing avatar image
JustinKing answered
I personally would create a CLR function that you can run RegEx expressions across each of the fields using a function to pass in the string you wish to match. Here is an article on creating a CLR object and using regex expressions to match result. http://msdn.microsoft.com/en-us/magazine/cc163473.aspx Here is a link for a free regex designer that helps you create them http://www.radsoftware.com.au/regexdesigner/
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I am a huge fan of RegEx and I would do exactly this if you need the full power of RegEx. It is however probably more work and less efficient than using the pattern matching available within T-SQL when that pattern matching is sufficient.
0 Likes 0 ·
Related to RegEx tools, I personally prefer [Expresso][1] by Ultrapico, which is also free and probably one of the best. :-) [1]: http://www.ultrapico.com/Expresso.htm
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.