在不改變 T-SQL 中數值位置的情況下反轉字元串的字元

使用 T-SQL，我試圖找到最簡單的方法來反轉字元串的字元而不改變數值的位置。

所以對於字元串：

abc223de11 

有

edc223ba11

我對此並不感到自豪，但你可以在 T-SQL 中做到這一點。這個內聯表值函式將字元串分解為一組字元，並將行號僅應用於字元串值，這樣您就可以顛倒設置。

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

以下呼叫：

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

產生以下結果：

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

在 SQL Server 2017 中，您可以使用以下命令將字元串與可預測的順序一起打包STRING_AGG()：

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

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

在舊版本中，您需要使用FOR XMLhack：

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)');

兩種情況的結果：

OriginalString  QuasiReversed
--------------  -------------
abc223de11      edc223ba11

引用自：https://dba.stackexchange.com/questions/230976