Reverse order of elements in a string

Question

I have the following string:

1119/2/483/11021

I would like to reverse the order of the elements in that string. Desired output:

11021/483/2/1119

T-SQL Version 2014

Wonted

@Aaron true about that, but that's why I commented and didn't close-vote. I agree I may have commented too hastily. – Outworn

So you want to split and concat? – Outworn

Answer 1

You need an ordered split function, e.g. (inspiration):

CREATE FUNCTION dbo.SplitOrdered
(
    @list    nvarchar(max), 
    @delim   nvarchar(10)
)
RETURNS TABLE 
WITH SCHEMABINDING 
AS 
RETURN
(
  WITH w(n) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0)) w(n)),
       k(n) AS (SELECT 0 FROM w a, w b),
       r(n) AS (SELECT 0 FROM k a, k b, k c, k d, k e, k f, k g, k h),
       p(n) AS (SELECT TOP (COALESCE(LEN(@list), 0)) 
                ROW_NUMBER() OVER (ORDER BY @@SPID) -1 FROM r),
       spots(p) AS 
       (
         SELECT n FROM p 
         WHERE (SUBSTRING(@list, n, LEN(@delim + 'x') - 1) LIKE @delim OR n = 0)
       ),
       parts(p,val) AS 
       (
         SELECT p, SUBSTRING(@list, p + LEN(@delim + 'x') - 1, 
           LEAD(p, 1, 2147483647) OVER (ORDER BY p) - p - LEN(@delim)) 
         FROM spots AS s
       )
       SELECT listpos = ROW_NUMBER() OVER (ORDER BY p), 
              Item    = LTRIM(RTRIM(val))
         FROM parts
);

Then you can reassemble using STRING_AGG() (if SQL Server 2017 or better) or FOR XML PATH on lower versions:

SQL Server 2017 +

DECLARE @OriginalString nvarchar(255) = N'1119/2/483/11021';

SELECT NewString = STRING_AGG(o.Item, N'/') 
                   WITHIN GROUP (ORDER BY listpos DESC)
  FROM dbo.SplitOrdered(@OriginalString, N'/') AS o;

SQL Server < 2017

DECLARE @OriginalString nvarchar(255) = N'1119/2/483/11021';

SELECT NewString = STUFF(
  (SELECT N'/' + o.Item 
   FROM dbo.SplitOrdered(@OriginalString, N'/') AS o
   ORDER BY o.listpos DESC
   FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'),1,1,N'');

• Example db<>fiddle

Director

Impressive answer, but all of that code makes me go "you better implement this in application logic". – Outworn

@Outworn Why? If splitting strings is a common business requirement in SQL Server, create a function with a logical name and never think about the code again. (That said, I agree with you, concatenated strings don't belong in SQL Server at all, and I argue for better design and TVPs all the time. But sometimes we can't just completely overhaul the system when we need to solve a single query.) – Director

not working right , the result is same value – Wonted

@elhassanahmed Please try again (observe the edit), change ORDER BY o.listpos to ORDER BY o.listpos DESC. – Director

Application logic aka SQLCLR in my opinion. Either that or just use ROW_NUMBER over STRING_SPLIT and hope for the best – Gomulka

@Gomulka I am in the middle of a massive project to actually eliminate CLR from our entire codebase because (a) it's not needed anymore since native splitting etc. are just as fast and (b) it's a blocker for Linux and cloud. Not sure why we can't have a function that performs well and guarantees order instead of just crossing our fingers and hoping we don't get mud. – Director

@Aaron sure, I'm all for creating functions for common tasks. Lately I've been making/adding/modifying "polyfill" functions for an older MySQL server to handle some particular GUID requirements. It's just that I find the SQL pretty unreadable and unmaintainable, with all its magic one-letter functions and zero comments on what it's actually doing. If it's a decent implementation you import it once and never touch it again, sure. – Outworn

@Outworn There are lots of ways to make it easier to read. I grabbed the best-performing function I've found to date from here and wanted to minimize changes to the original (but I had already adapted it somewhat for my own needs). – Director

thanks for reply , can i apply this function on column in table – Wonted

@elhassan yes - SELECT * FROM dbo.t AS t CROSS APPLY dbo.SplitOrdered(t.column, '/') AS o; – Director

Answer 2

Please try the following solution based on the built-in PARSENAME() T-SQL function.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Tokens VARCHAR(MAX));
INSERT INTO @tbl (Tokens) VALUES
('1119/2/483/11021'),
('1120/25/484/1102');
-- DDL and sample data population, end

SELECT tbl.*
    ,  PARSENAME(c, 1) + '/' +
        PARSENAME(c, 2) + '/' +
        PARSENAME(c, 3) + '/' +
        PARSENAME(c, 4) AS Result
FROM @tbl AS tbl
    CROSS APPLY (VALUES (REPLACE(Tokens, '/', '.') )) AS t(c);

Output

+----+------------------+------------------+
| ID |      Tokens      |      Result      |
+----+------------------+------------------+
|  1 | 1119/2/483/11021 | 11021/483/2/1119 |
|  2 | 1120/25/484/1102 | 1102/484/25/1120 |
+----+------------------+------------------+

Steward

PARSENAME() is fun, but this solution relies on knowing there are always exactly 4 elements and no element will ever exceed 128 characters. – Director

I agree, but it is based on a data sample that the OP provided. – Steward

Right I'm just making the OP aware why it breaks if all their sample data isn't exactly 4 elements <= 128 characters (I tend to assume that all their data doesn't look identical to the one row provided). – Director

i need to apply your code on column in table – Wonted

My answer is tailored for a column in a table – Steward

@elhassanahmed You didn't bother telling us your table and column name in your question, so we have to make stuff up. Just take the SELECT and replace @tbl with your table name and Tokens with your column name. – Director

thanks for reply , can i apply this function on column in table – Wonted

Definitely, you can. Please don't forget to mark the proposed solution as answer. – Steward

As it was already mentioned; "Just take the SELECT and replace @tbl with your table name and Tokens with your column name" – Steward

Answer 3

First, split the string and convert it into a column then order by desc and display into multiple row values into a single row. In the following code, you can set any string and split char.

Try following way.

DECLARE @S varchar(max) ,
        @Split char(1),
        @X xml

DECLARE @Names VARCHAR(8000) 

SELECT @S = '1119/2/483/11021',
       @Split = '/'

SELECT @X = CONVERT(xml,' <root> <myvalue>' +
REPLACE(@S,@Split,'</myvalue> <myvalue>') + '</myvalue>   </root> ')

select  @Names = COALESCE(@Names + '/', '') + Value  from (
select  rowno,Value from (
select ROW_NUMBER() OVER(ORDER BY d) AS rowno  , Value from (
SELECT  T.c.value('.','varchar(20)') as Value,0 as d
 FROM @X.nodes('/root/myvalue') T(c)
 ) m
 ) r 
 ) t  order by t.rowno desc

select @Names as ReverseString

Okechuku

thanks for reply , can i apply this function on column in table – Wonted

Yes you can. Here I shared logic. You can convert into function and use it as per you requirements. – Okechuku

If it works fine, please, don't forget to mark it as an accepted answer ;) thank you – Okechuku

Answer 4

Splitting the string into sub-strings, and then joining them back up, is most likely going to be a good approach.

Some comments mention using string-reverse, but that doesnt seem to be a good approach at all in your case, since you just want to reverse the order of words within the current string, not actually reverse the entire text-string character-by-character.

PS: string_split does not guarantee the order of the chunks!

Moraine

string_split does not guarantee the order of the chunks. – Georgianngeorgianna