Add Comma to a String in Snowflake

Sounds like a job for String Functions (Regular Expressions)

with cities(city) as (
  select * from values
  ('Austin TX'),
  ('Nashville TN'),
  ('Minneapolis MN'),
  ('St. Louis MO')
)
select regexp_replace(city, '(.*) (.*)', '\\1, \\2') as city
from cities;

A good site to learn more about regular expressions Regular Expressions Info

I would inclined to use Dave's answer. But you answer can be fixed:

select 
    column1 as b2_loc
    ,(b2_loc ||', '|| (RIGHT(b2_loc, 2))) AS wrong
    ,trim(substring(b2_loc,0, length(b2_loc)-2)) ||', '|| (RIGHT(b2_loc, 2)) AS correct
from values
('Austin TX')
;

the reason this happens is, you are get all of the input string then attaching the comma and the last two values.. thus you don't want all of the input, just the minus 2 part of it, and then the whitespace also trimmed. Unfortunately you cannot use -2 in the substring to make it right side relative (like you can is some other languages) so LENGTH also needs to be used, then TRIM to remove whitespace.

Assuming state abbreviations are always 2 characters preceded by a single space, you could use insert

set str='Austin TX';

select insert($str,length($str)-2,0,',')

Alternatively, you could also reverse the string, insert your comma, and reverse it back

select reverse(insert(reverse($str),4,0,','));