mehtat_90 mehtat_90 - 1 year ago 74
SQL Question

Split Address column into multiple columns

I have an address field where all the address details are held in one column, I want to create some labels so need to be able to split the address into the correct format.
Example :-

ADDRESS
PIKE ROAD, AL 36064-3401
MEMPHIS TN 38104-5802
JAMAICA PLAIN MA 02130-2337


Need to split this column into

City State Zip
PIKE ROAD AL 36064-3401
MEMPHIS TN 38104-5802
JAMAICA PLAIN MA 02130-2337


I am able to extract Zip code using

STUFF(Address, 1, Len(Address) +1- CHARINDEX(' ',Reverse(Address)), '') from abx


but I am having trouble in extracting city and state. Is it possible to split the string based on the length of words, i.e. all the Characters before the length of the word (2) goes in City and all the words with 2 characters goes in state example: - Pike Road goes into the City and AL (length is 2) in the state?

Answer Source

As @Habo said, you only need to use LEN and SUBSTRING.

WITH Tbl AS(
    SELECT * FROM (VALUES       
        ('PIKE ROAD, AL 36064-3401'),
        ('MEMPHIS TN 38104-5802'),
        ('JAMAICA PLAIN MA 02130-2337')
    ) t(Address)
)
SELECT
    City    = SUBSTRING(Address, 0, LEN(Address) - 13),
    State   = SUBSTRING(Address, LEN(Address) - 12, 2),
    ZipCode = SUBSTRING(Address, LEN(Address) - 9, 10)
FROM Tbl
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download