3

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
3
  • 2
    Reversing strings is surprisingly tricky if there are Unicode characters. T-SQL is poorly suited for such a task. Would it be possible to use, say, .Net instead? Either on application level or via CLR?
    – vonPryz
    Commented Feb 28, 2019 at 16:55
  • 1
    And to ask a silly question, why? Also, what version of SQL Server? Commented Feb 28, 2019 at 17:37
  • I'm using SSMS17, It's just for my personal training I just came up with this scenario to test out my t-sql skills to see if it's possible to solve it with t-sql. Because in other languages we could possibly swap characters within string easily and quickly but I couldn't do it with t-sql.
    – anna
    Commented Feb 28, 2019 at 18:43

1 Answer 1

7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.