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