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

CodePudding user response：

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'.[1]', N'nvarchar(max)'),1,1,N'');

• Example db<>fiddle

CodePudding user response：

If you use SQL Server 2017 , a JSON-based approach is also an option. The idea is to transform the input text into valid JSON array (1119/2/483/11021 into ["1119","2","483","11021"]), parse this array with OPENJSON() and aggregate the substrings with STRING_AGG() using the appropriate order. In case of a JSON array, OPENJSON() returns a table with columns key, value and type and the key column returns the index of each item in the specified array.

DECLARE @text nvarchar(max) = N'1119/2/483/11021'

SELECT @text = 
   STRING_AGG([value], N'/') 
   WITHIN GROUP (ORDER BY CONVERT(int, [key]) DESC)
FROM OPENJSON(CONCAT(N'["', REPLACE(@text, N'/', N'","'), N'"]'))

SELECT @text

Result:

11021/483/2/1119

CodePudding user response：

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

CodePudding user response：

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.