Ok, now assuming I already had City & State columns, how would I use this logic to update those 2 fields from CityState?


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