Greetings,

This is an Oracle command which will show the state only. Perhaps you can adapt it to SQL Server.

SUBSTR(CityState,1, INSTR(CityState, ' ', -1))


>I have a column called CityState. It has 14 rows:
>
>

>CityState
>------------------------------------
>BREA  CA
>SAN DIEGO CA
>NORTHBROOK IL
>SAN DIEGO
>ENCINITAS
>VISTA
>ENCINITAS
>POWAY
>CARLSBAD CA
>LINCOLN  NE
>SAN MARCOS CA
>OCEANSIDE
>ENCINITAS
>OCEANSIDE

>
>I'm trying to split out the city & state:
>

>SELECT	CityState,
>		CASE 
>			WHEN LBCMS.dbo.OCCURS(' ', CityState) = 0 THEN CityState 
>			WHEN LBCMS.dbo.OCCURS(' ', CityState) = 1 THEN SUBSTRING(CityState, 1, LBCMS.dbo.AT(' ', CityState, 1))
>			WHEN LBCMS.dbo.OCCURS(' ', CityState) = 2 THEN SUBSTRING(CityState, LBCMS.dbo.AT(' ', CityState, 2), 200)
>		END AS City
>	FROM ContactInfo1
>	 WHERE CityState IS NOT NULL
>

>
>I'm getting this:
>
>

>CA
>CA
>NORTHBROOK 
>SAN 
>ENCINITAS
>VISTA
>ENCINITAS
>POWAY
>CARLSBAD 
>NE
>CA
>OCEANSIDE
>ENCINITAS
>OCEANSIDE

>
>Anyone see what's wrong?