CODEWITHSUNDEEP
sqlstringparsingsql-server-2012
user106279
user106279

Reputation: 37

SQL parsing string of address with spaces and commas into seperate fields

I have this list of data:

    ADDRESS
    '204 W 8th St, ABC, New York, NY 12345-6789'
    '222 N Barley St, Pittsburh, Pennsylvania, PA 98765-4321'
    '1 S Main St, Good Day, Washington, PA 13579-2468'
    '232 Justin Blvd, Sacramento, CA 86420-7531'

Where I would like to parse into 5 fields ie Mail Address 1, Mail Address 2, City, State, Zip Code. I been able to parse some of these individual though not correctly such as:

    select distinct StreetName =
    substring(ADDRESS, CHARINDEX(',', ADDRESS+',', 1) +1,
    CHARINDEX(',', ADDRESS+',', CHARINDEX(',', ADDRESS+',', 1) +1) -
    CHARINDEX(',', ADDRESS+',', 1) -1)
    from Bills
    where ISNUMERIC(LEFT(ADDRESS,1))=1
    AND LEN(ADDRESS) > 1

which gets me mail address 2 mostly. How can I do this so that the string is separated into 5 columns?

Upvotes: 2

Views: 495

Answers (1)

Serg
Serg

Reputation: 22811

Parsing address may be rather tricky task and I realy don't know all the rules. I'll just show how to make calculations step by step in SQL much like in other programming languages. Use CROSS/OUTER APPLY.

select sd.addr, t.*, tt.*, ttt.*
from (
   select '204 W 8th St, ABC, New York, NY 12345-6789' as addr
) sd
cross apply(
    select nParts = len(addr) - len(replace(addr,',',''))
    , lastPos = len(addr) - charindex(',', reverse(addr),1) +1
    , secondPos = charindex(',' ,addr,1)
    ) t
cross apply(
    select first = left(addr, secondPos-1)
    ,middle = substring(addr, secondPos+1, lastPos - secondPos -1)
    ,last = right(addr, len(addr) - lastPos )
    ) tt
outer apply( 
    select thirdPos = charindex(',' , middle ,1)
    ,forthPos = len(middle) - charindex(',', reverse(middle),1)+1
    where nParts >3    
    ) ttt

and so on. Add steps and logic as needed.

Upvotes: 1

Related Questions