>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.
>
>
Nothing, but (1) storing the states in a table or (2) consider trailing parts with two chars as states