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.
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
This is pulling records when I search for a number but not the string value. Ex: GA for Georgia comes back with zero records.
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%
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/
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.
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
