This works for these three examples. As @Kevin pointed out above, this works if your data is consistent, which is, as he said, "a very big if."

What I did was create a subquery mimicking a table. It has one column, "x", that just has a string value. I worked backwards to get the zip code first (which you figured out), then the state, then the street address. The function(s) used to extract each piece of information build upon the previous one.

I haven't used SQL Server in years, so I used a web app designed to mimick SQL Server 2014 .

This query should produce the table in the screenshot below:

select x
, REPLACE(SUBSTRING(x, 1, LEN(x) - CHARINDEX(' ', REVERSE(x), CHARINDEX(' ', REVERSE(x)) + 1)), ',', '') as city
, SUBSTRING(x, LEN(x) - CHARINDEX(' ', REVERSE(x), CHARINDEX(' ', REVERSE(x)) + 1) + 2, 2) as state
, SUBSTRING(x, LEN(x) + 2 - CHARINDEX(' ', REVERSE(x)), CHARINDEX(' ', REVERSE(x))) as zip

FROM (
select 'PIKE ROAD, AL 36064-3401' as x 
union
select 'MEMPHIS TN 38104-5802'
union
select 'JAMAICA PLAIN MA 02130-2337'
    ) as whatever

HTH!

Cheers,

-Maashu

You can do something like this, and it will work if your data is consistent. That is a very big IF...

DECLARE @ADDRESS NVARCHAR(255) = 'PIKE ROAD, AL 36064-3401'
DECLARE @DELIMITER CHAR(1) = ' '
DECLARE @POS INT
DECLARE @ZIP NVARCHAR(11)
DECLARE @STATE NVARCHAR(11)
DECLARE @CITY NVARCHAR(200)

-- get the occurrence of the last space
SET @POS = LEN(@ADDRESS) - CHARINDEX(@DELIMITER,REVERSE(@ADDRESS))

--set the zip code
SET @ZIP = SUBSTRING(@ADDRESS, @POS+2, 11)

--get the remaining portion of the address
SET @ADDRESS = SUBSTRING(@ADDRESS, 0, @POS+1)

--set the last space again
SET @POS =  LEN(@ADDRESS) - CHARINDEX(@DELIMITER,REVERSE(@ADDRESS))

--set the state and street
SET @STATE = SUBSTRING(@ADDRESS, @POS+2, 11)
SET @CITY = SUBSTRING(@ADDRESS, 0, @POS)

PRINT @ZIP
PRINT @STATE
PRINT @CITY

OUTPUT:

36064-3401
AL
PIKE ROAD

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

If your data is consistent and delimited, you may use the following:

SELECT 
  REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 1)) AS [Street],
  REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 2)) AS [City],
  REVERSE(PARSENAME(REPLACE(REVERSE(myAddress), ',', '.'), 3)) AS [State]
FROM dbo.custAddress;

The above table has a column that have values of "steet, city, state" per row. Please look at www.mssqltips.com for the entire explanation and examples.