Try this:

declare @CityState varchar(30)
set @CityState = 'CARLSBAD'

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

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