How to reverse string in column a word by word?

Question

I have a table with a varchar(50) column 'Relation' having more than 1000 rows of data like:

 P1_P10_P45_P20
 P1_P14_P5_P22
 P1_P3
 P3_P4_P5_P2_P100_P2_P1

I want the output to have reverse order:

 P20_P45_P10_P1
 P22_P5_P14_P1
 P3_P1
 P1_P2_P100_P2_P5_P4_P3

Could you please help me achieve this in single query?

Hello, this isn't a code writing service. You should first attempt to solve the issue yourself and then ask about specific issues (or the issue as a whole, but show your attempt to solve it). Check out [how to ask a good question](https://stackoverflow.com/help/how-to-ask). — Morgosus, Sep 10 '20 at 14:35
Fix your data model! The root problem is that you are storing multiple values in a string. — Gordon Linoff, Sep 10 '20 at 14:42
@Aditi v18.5 is the version of the SQL Server Management Studion (a client tool). What is the SQL Server version (what does `SELECT @@VERSION;` return)? Thanks. — Zhorov, Sep 11 '20 at 05:52

DhruvJoshi · Answer 1 · 2020-09-11T07:09:28.330

Aditi you can use a Tally table to find all _ and then join them back using STUFF + FOR XML PATH combination like below.

I recommend that you read about Tally tables at earliest possible time here

Also the demo link is here

--create table yourtable(Relation nvarchar(50));
--insert into yourtable values 
-- ('P1_P14_P5_P22'),
--   ('P1_P3'),
--   ('P3_P4_P5_P2_P100_P2_P1'), ('P1_P3'),
--   ('P3_P4_P5_P2_P100_P2_P1');


;WITH Tally AS (
   SELECT 1 as Num
   UNION ALL
   SELECT Num + 1 FROM Tally  WHERE Num < 51
    )
,
InputSet AS
(
 select *, RN=row_number() over (order by (select 1)) from yourtable
    )
,TempSet AS
(
      SELECT 
        Relation, 
        Num,
        RN,
        partBetweenUnderscore = SUBSTRING(Relation, Num, ISNULL(LEAD(Num) OVER (Partition by RN ORDER BY Num ASC),LEN('_'+Relation)+1)-Num-1)
    FROM
    (
        SELECT *  
        FROM InputSet CROSS JOIN Tally 
        WHERE CHARINDEX('_','_'+Relation,Num)=Num
     )T
 )
SELECT 
   Relation,
    NewRelation = STUFF(
                    (SELECT '_' + T1.partBetweenUnderscore FROM TempSet T1 WHERE T1.RN=T2.RN ORDER BY T1.Num DESC FOR XML PATH ('') 
                        ),1,1,'')
FROM TempSet T2
GROUP BY RN, Relation

If the column 'Relation' have more than 1 rows it shows error 'Invalid parameter length passed to left or substring function'. Here is the modification done in your link by inserting another row: [link](https://rextester.com/JVAX56882) — Aditi, Sep 11 '20 at 06:18
@Aditi updated my answer to support multiple rows. The last edit also handles duplicate relation column data — DhruvJoshi, Sep 11 '20 at 07:09

Zhorov · Accepted Answer · 2020-09-17T07:13:36.423

You need to split the stored strings using a splitter, that returns the substrings and the position of each substring. After that you can easily build the desired output.

If you use SQL Server 2017+, you may try a JSON-based approach. You need to transform each string into a valid JSON array (for example P1_P10_P45_P20 into ["'P1","P10","P45","P20"]), parse this array as a table with OPENJSON() and join the rows with STRING_AGG() to generate the expected output:

Table:

CREATE TABLE Data (Relation varchar(50))
INSERT INTO Data (Relation)
VALUES
   ('P1_P10_P45_P20'),
   ('P1_P14_P5_P22'),
   ('P1_P3'),
   ('P3_P4_P5_P2_P100_P2_P1')

Statement:

SELECT c.Relation
FROM Data d
OUTER APPLY (
   SELECT STRING_AGG([value], '_') WITHIN GROUP (ORDER BY CONVERT(int, [key]) DESC)
   FROM OPENJSON(CONCAT('["', REPLACE(d.Relation, '_', '","'), '"]'))
) c (Relation)

Result:

Relation
----------------------
P20_P45_P10_P1
P22_P5_P14_P1
P3_P1
P1_P2_P100_P2_P5_P4_P3

Kevin Nelson · Answer 3 · 2020-09-10T17:18:38.820

First all of the prievious comments are correct, especially that this is a data model problem. Here is a very kludgy solution. I offer it because you only have 1000 records. This is not efficient, nor will it scale up. The following works on MS SQL Server 2017.

Drop table if exists Relation   
create table Relation (Relation varchar(50))
INSERT INTO Relation (Relation)
VALUES
    ('P1_P10_P45_P20'),
    ('P1_P14_P5_P22'),
    ('P1_P3'),
    ('P3_P4_P5_P2_P100_P2_P1');



DROP TABLE IF EXISTS Rev
create table Rev (Relation varchar(50), Rev varchar(50))
DROP TABLE IF EXISTS Z
create table Z (token varchar(50))
declare @Reverse varchar(50)
set @Reverse = ''

declare @token varchar(50)
declare @Relation varchar(50)
declare cur cursor for select * from Relation
open cur
fetch next from cur into @Relation
while @@FETCH_STATUS = 0
begin   
    with T(Relation, starts, pos) as (
    select @Relation, 1, charindex('_', @Relation)
    union all
    select @Relation, pos + 1, charindex('_', @Relation, pos + 1)
    from T
    where pos > 0)
    insert into Z select substring(@Relation, starts, case when pos > 0 then pos - starts else len(@Relation) end) token
    from T
    declare cur2 cursor for select token from Z
    open cur2
    fetch next from cur2 into @token
    while @@FETCH_STATUS = 0
        begin
            set @Reverse =  @token + '_' + @Reverse  
            fetch next from cur2 into @token
        end
        close cur2
        deallocate cur2
        set @Reverse = (select left(@Reverse,len(@Reverse)-1))
        insert into Rev select @Relation, @Reverse
        set @Reverse = ''
        delete Z
    fetch next from cur into @Relation
end;
close cur
deallocate cur
select * from Rev



SELECT @@version

How to reverse string in column a word by word?

3 Answers3