字符串中元素的反向顺序 [英] Reverse order of elements in a string

问题描述

我有以下字符串：

1119/2/483/11021

我想颠倒该字符串中元素的顺序。所需输出：

11021/483/2/1119

T-SQL 2014版

推荐答案

您需要一个有序的拆分函数，例如(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
);

然后您可以在较低版本上使用STRING_AGG()(如果SQL Server 2017或更高版本)或FOR XML PATH重新组装：

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

• 示例db<>fiddle

这篇关于字符串中元素的反向顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！