Interesting, I thought substring (@Var, len(@Var), len(@Var)) will give you an empty string.


I can say, I have blonde moments myself :)

Change it to

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

select  charindex(' ',reverse(@CityState)), 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) +1 else 
len(@CityState) - charindex(' ',reverse(@CityState))+2 end, LEN(@CityState)) as State

select SUBSTRING(@citystate,len(@CityState)+1,len(@CityState)-1)
<pre>
>Try this:
>
><pre>
>
>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?