For one time thing it doesn't matter - even though my code didn't use any of the extra functions and gave you the same result.

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