Probably,

This is a one time thing. The ContactInfo1 is only temporary as I'm using it to get the contact data into more normalized tables.


>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 :)
>>>>>