Как поменять местами символы разделителями в строке?

Использование PARSENAME

declare @table table (addy varchar(64))
    insert into @table
    values
    ('NYC, NY State, USA'),
    ('London, xxx, England'),
    ('Missouri, St. Louis, USA')

select
    replace(
    parsename(replace(replace(addy,'.','^'),',','.'),2) 
    + ', ' 
    + parsename(replace(replace(addy,'.','^'),',','.'),3) 
    + ', ' 
    + parsename(replace(replace(addy,'.','^'),',','.'),1)
    ,'^','.') 
from @table

Это обычная функция для разделения строк по разделителю, если их 4 или меньше. В вашем случае 3. Нам просто нужно заменить запятую точкой. (и используйте дефис или что-то еще, когда должна быть точка)

STUFF, RIGHT, LEN, пара других манипуляций со строками.

select
    stuff(right(addy,len(addy) - charindex(',',addy)),charindex(',',right(addy,len(addy) - charindex(',',addy))),0, ', ' + substring(addy,0,CHARINDEX(',',addy)) )
from @table

Сначала ставим все до первой запятой:

substring(addy,0,CHARINDEX(',',addy))

Затем находим все после первой запятой:

right(addy,len(addy) - charindex(',',addy))

Наконец, мы объединили их с подмножествами вашей строки с STUFF для вставки первой части во вторую часть, начиная с запятой.

stuff(right(addy,len(addy) - charindex(',',addy)),charindex(',',right(addy,len(addy) - charindex(',',addy))),0, ', ' + substring(addy,0,CHARINDEX(',',addy)) )

Если вы хотите сделать это без использования String Spliter, то будет работать следующее (при условии, что адрес разделен на 3 части):

WITH VTE AS(
    SELECT *
    FROM (VALUES('NYC, NY State, USA'),
                ('London, xxx, England')) V([Address]))
SELECT V.[Address],
       SUBSTRING([Address], CI1.CI +1, CI2.CI-CI1.CI) + LEFT(V.[Address], CI1.CI) + RIGHT(V.[Address], LEN(V.[Address]) - CI2.CI)
FROM VTE V
     CROSS APPLY(VALUES(CHARINDEX(',',V.[Address]))) CI1(CI)
     CROSS APPLY(VALUES(CHARINDEX(',',V.[Address], CI1.CI + 1))) CI2(CI);

Если, однако, вы можете использовать разделитель, который обеспечивает порядковую позицию (например, delimitedsplit8k), вы можете сделать что-то вроде этого:

USE Sandbox;
Go

WITH VTE AS(
    SELECT *
    FROM (VALUES('NYC, NY State, USA'),
                ('London, xxx, England')) V([Address]))
SELECT V.[Address],
       STUFF((SELECT ',' + item
              FROM dbo.DelimitedSplit8K(V.[Address],',') DS
              ORDER BY CASE DS.ItemNumber WHEN 1 THEN 2
                                          WHEN 2 THEN 1
                                          ELSE DS.ItemNumber END
              FOR XML PATH('')),1,1,'')
FROM VTE V;

Возможно другой вариант

Пример

Declare @YourTable table (ID int,[Address] varchar(150))
Insert Into @YourTable values 
 (1,'NYC, NY State, USA')
,(2,'London, xxx, England')

Select A.ID
      ,NewString = concat(Pos2,', '+Pos1,', '+Pos3)
 From  @YourTable A
 Cross Apply (
                 Select Pos1 = n.value('/x[1]','varchar(max)')
                       ,Pos2 = n.value('/x[2]','varchar(max)')
                       ,Pos3 = n.value('/x[3]','varchar(max)')
                  From  (Select cast('<x>' + replace([Address],',','</x><x>')+'</x>' as xml) as n) X
             ) B

Возврат

ID   NewString
1    NY State, NYC,  USA
2    xxx, London,  England