[SOLVED] Split Address column into multiple columns

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?

Solution

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