STRING_SPLIT from column value and reverse it in SQL Server

Question

I am trying to reverse string values in a column in SQL Server.

What I am trying to achieve is, when the value of the column is child/parent, I want to change it to parent/child when child contains '112'

For example I want to change columns with value Reports/112-Major to 112-Major/Reports

To start with, I tried to use STRING_SPLIT and append them like String_Split(ColumnName,'/')[1] + String_Split(ColumnName,'/')[0] if String_Split(ColumnName,'/')[1] like '%112%'

Most of the examples that I see online have something like

SELECT Value FROM STRING_SPLIT('Lorem/ipsum/dolor/sit/amet.', '/');

But I want to split and then merge based on condition and then update the column

Something like,

update  tblTableName
set siteUrl = String_Split(ColumnName,'/')[1] + String_Split(ColumnName,'/')[0]
where `String_Split(ColumnName,'/')[1] like '%112%'

Is there a way to do this in SQL Server?

thanks for asking. updated the question with one example. Please let me know if it helps — Vignesh Subramanian, Jan 01 '18 at 13:14

score 3 · Answer 1 · answered Jan 01 '18 at 13:10

3

You can use this expression:

stuff(col, 1, charindex('/', col), '') + '/' + left(col, charindex('/', col) - 1)

answered Jan 01 '18 at 13:10

Gordon Linoff

1,242,037
58
646
786

score 2 · Answer 2 · answered Jan 01 '18 at 14:40

Another option just for fun.

Gordon's solution would be my first choice and is certainly more performant (+1), but the following illustrates a simple technique which can be used to split a string into columns and not rows.

Sample

Declare @YourTable table (ID int,YourCol varchar(100))
Insert Into @YourTable values
(1,'Reports/112-Major'),
(2,'Reports/Something Else')

Update @YourTable Set YourCol = Pos2+'/'+Pos1
 From  @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(A.YourCol,'/','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as B1 
             ) B
 Where Pos2 Like '%112%'

Updated Results

ID  YourCol
1   112-Major/Reports
2   Reports/Something Else

STRING_SPLIT from column value and reverse it in SQL Server

2 Answers2