T-SQL – Reverse Characters of a String Without Changing Numeric Values

sql serverstringt-sql

With T-SQL, I'm trying to find the easiest way to reverse the characters of a string without changing the position of the numeric value.

So for string:

abc223de11

have 

edc223ba11

Best Answer

I am not proud of this, but you can do it in T-SQL. This inline table-valued function breaks the string into a set of characters, and applies row numbers to just the string values, so that you can flip the set upside down.

CREATE FUNCTION dbo.SortString
(
  @s varchar(64)
)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN 
  ( -- get a sequence number for every character in @s
    WITH n AS 
    (
      SELECT n = 1 UNION ALL SELECT n + 1 FROM n WHERE n < LEN(@s)
    ),
    s AS 
    ( -- break out each character, apply sequence number, test numeric
      SELECT n, s = SUBSTRING(@s, n, 1), isn = ISNUMERIC(SUBSTRING(@s, n, 1))
      FROM n
    ),
    s2 AS
    ( -- apply reverse match pointers, but only for strings
      SELECT n,s, 
        rn1 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 
              (PARTITION BY isn ORDER BY n ASC) END,
        rn2 = CASE WHEN isn = 0 THEN ROW_NUMBER() OVER 
              (PARTITION BY isn ORDER BY n DESC) END
      FROM s
    )
    SELECT s2.n, New = COALESCE(s3.s, s2.s), Original = s2.s
        FROM s2 LEFT OUTER JOIN s2 AS s3
        ON s2.rn2 = s3.rn1
  );
GO

The following call:

DECLARE @str varchar(64) = 'abc223de11'; 
SELECT Original, New FROM dbo.SortString(@str) ORDER BY n;

Yields the following results:

Original  New
--------  ---
a         e
b         d
c         c
2         2
2         2
3         3
d         b
e         a
1         1
1         1

In SQL Server 2017, you can pack the string back together with predictable order using STRING_AGG():

DECLARE @str varchar(64) = 'abc223de11'; 

SELECT 
  OriginalString = @str, 
  QuasiReversed = STRING_AGG(New,'') WITHIN GROUP (ORDER BY n) 
FROM dbo.SortString(@str);

In older versions, you'll need to use a FOR XML hack:

DECLARE @str varchar(64) = 'abc223de11'; 

SELECT 
  OriginalString = @str, 
  QuasiReversed = (SELECT '' + New
    FROM dbo.SortString(@str) 
    ORDER BY n
    FOR XML PATH, TYPE).value(N'.[1]','varchar(64)');

Results in both cases:

OriginalString  QuasiReversed
--------------  -------------
abc223de11      edc223ba11
Related Question