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?
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