Cfxtrjtrk

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

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