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

Good idea - you're right. If the len of last string is more than 2, then it's not a state.