Parsing an Address with T-SQL

Question

Hello,

I am trying to figure out how to parse an address using T-SQL and I suck at T-SQL. My challenge is this,

I have a table called Locations defined as follows:

- City [varchar(100)]
- State [char(2)]
- PostalCode [char(5)]

My UI has a text box in which a user can enter an address in. This address could be in the form of essentially anything (yuck, I know). Unfortunately, I cannot change this UI either. Anyways, the value of the text box is passed into the stored procedure that is responsible for parsing the address. I need to take what the person enters and get the PostalCode from the Locations table associated with their input. For the life of me, I cannot figure out how to do this. There are so many cases. For instance, the user could enter one of the following:

• Chicago, IL
• Chicago, IL 60601
• Chicago, IL, 60601
• Chicago, IL 60601 USA
• Chicago, IL, 60601 USA
• Chicago IL 60601 USA
• New York NY 10001 USA
• New York, NY 10001, USA

You get the idea. There are a lot of cases. I can't find any parsers online either. I must not be looking correctly. Can someone please point me to a parser online or explain how to do this? I'm willing to pay for a solution to this problem, but I can't find anything, I'm shocked.

Answer 1

Perhaps a CLR function might be a better choice than tsql. Check out http://msdn.microsoft.com/en-us/magazine/cc163473.aspx for an example of using regular expressions to parse some pretty complex string inputs into table value results. Now you get to be as creative as you please with your regex matching but the following regex should get you started:

(.*?)([A-Z]{2}),? (\d+)( USA)?$

If you're reluctant to use CLR functions, perhaps you have regex functionality in the calling system, like ASP.Net or PHP.