CODEWITHSUNDEEP
sqlsql-server
Jill Clover
Jill Clover

Reputation: 2318

How to swap chars by delimiters in a string?

Let's say I have a column Address

Address
NYC, NY State, USA
London, xxx, England

I want to swap the chars before and after the first delimiter ','.

The desired output is

Address
NY State, NYC, USA
xxx, London, England

I think substring or charindex may help, but I am not sure how.

Upvotes: 0

Views: 119

Answers (3)

S3S
S3S

Reputation: 25112

Using 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

This is a common function to break up strings by a delimiter when there are 4 or lesser parts. In your case, 3. We simply need to replace the comma with a period. (and use a hyphen or something for when there should be a period)

STUFF, RIGHT, LEN, a couple other string manipulations.

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

First we get everything up the first comma:

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

Then, we find everything after the first comma:

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

Finally, we combined these to subsets of your string with STUFF for insert the first part, into the second part, starting at the comma.

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

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81930

Perhaps another option

Example

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

Returns

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

Upvotes: 2

Thom A
Thom A

Reputation: 95554

If you want to do this without using a String Spliter, then the following would work (provided the address is split into 3 parts):

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

If, however, you can use a splitter which provides to ordinal position (for example delimitedsplit8k), you can do something like this:

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;

Upvotes: 0

Related Questions