I'm thinking we could have saved some time if we would put dbo.occurs as a field in derived table.

e.g.

;with cte as (select CityState, City, State, dbo.occurs(' ',CityState) as  NumSpaces from myTable)

update cte set City = case when NumSpaces = 0 then... etc.

>Here's what I came up with:
>
>

>UPDATE ContactInfo1
>	SET City =	CASE WHEN lbcms.dbo.occurs(' ', CityState) = 0 THEN lbcms.dbo.getwordnum(CityState, 1, ' ')
>					 WHEN lbcms.dbo.occurs(' ', CityState) = 1 THEN lbcms.dbo.getwordnum(CityState, 1, ' ')
>					 WHEN lbcms.dbo.occurs(' ', CityState) = 2 THEN SUBSTRING(CityState, 1, lbcms.dbo.at(' ', CityState, 2))
>				END,
>		State = CASE WHEN lbcms.dbo.occurs(' ', CityState) = 0 THEN NULL
>					 WHEN lbcms.dbo.occurs(' ', CityState) = 1 THEN lbcms.dbo.getwordnum(CityState, 2, ' ')
>					 WHEN lbcms.dbo.occurs(' ', CityState) = 2 THEN lbcms.dbo.getwordnum(CityState, 3, ' ')
>				END
>	WHERE CityState IS NOT NULL
>
>

>
>It produced these results:
>

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

>
>Row 4 wrong, but I don't see that anything can be done there.
>
>
>
>>>Ok, now assuming I already had City & State columns, how would I use this logic to update those 2 fields from CityState?
>>>
>>>
>>
>>Ok, never mind.
>>

>>update myTable set City = charindex(' ',reverse(CityState)), substring(CityState, 1, 
>>case when charindex(' ',reverse(CityState)) = 0 then len(CityState) 
>>else len(CityState) - charindex(' ',reverse(CityState)) end), state = 
>>SUBSTRING(CityState, case when charindex(' ',reverse(CityState)) = 0 then len(CityState) +1 else 
>>len(CityState) - charindex(' ',reverse(CityState))+2 end, LEN(CityState)) 
>>

>>>>Interesting, I thought substring (@Var, len(@Var), len(@Var)) will give you an empty string.
>>>>
>>>>
>>>>I can say, I have blonde moments myself :)
>>>>