Kevin,

Try

select  substring(CityState, 1, 
case when charindex(' ',reverse(CityState)) = 0 then len(CityState) 
else len(CityState) - charindex(' ',reverse(CityState)) end) as City,
SUBSTRING(CityState, case when charindex(' ',reverse(CityState)) = 0 then len(CityState) else 
len(CityState) - charindex(' ',reverse(CityState))+2 end, LEN(CityState)) as State

Also, in your data you have city with a space and no state. It will be parsed incorrectly.


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