Not sure, how or why you want to parse string like 'San Francisco, CA, USA' into 3 columns as this would still leave non-atomic data in one column, thus violating 1NF, but what about:

declare @string varchar(100)

set @string = 'San Francisco, CA, USA'

select

 parsename(replace(replace(@string,',',''),' ','.'),4)

 , parsename(replace(replace(@string,',',''),' ','.'),3)

 , parsename(replace(replace(@string,',',''),' ','.'),2)

 , parsename(replace(replace(@string,',',''),' ','.'),1)

set @string = 'Palos Verdes, CA'

select

  parsename(replace(replace(@string,',',''),' ','.'),3)

 , parsename(replace(replace(@string,',',''),' ','.'),2)

 , parsename(replace(replace(@string,',',''),' ','.'),1)