CODEWITHSUNDEEP
sqlsql-servert-sqlsplit
sdr
sdr

Reputation: 11

How to split a street address in SQL Server Management Studio

I have a column that contains a street address. Below are some examples of how that street address could be.

Question is: how do I break this into individual columns for str_number, str_prefix (may or may not exist), Str_Name (could be one or more words), str_type, str_suffix (may or may not exist).

I'm not sure if this is possible in SQL, with some values not being there but thought I would check. Thank you very much for any assistance.

Upvotes: 0

Views: 371

Answers (1)

alroc
alroc

Reputation: 28194

You can't do this reliably, as there are far too many variations on how addresses are formatted, abbreviated, etc. See Falsehoods programmers believe about addresses.

You would be much better off writing/finding an application which submits each address to a service (API) which can then look up against a known address database and return the constituent components in a structured format, then insert that "cleansed" data into proper fields in the database.

Upvotes: 4

Related Questions