时间戳

首页 > 解决方案 > 使用 string_split 函数查询更新字符串

sql - 使用 string_split 函数查询更新字符串

问题描述

我正在尝试更新表中数据格式如下的列：

Id | ColA
----------
1    Peter,John:Ryan,Jack:Evans,Chris
2    Peter,John:Ryan,Jack
3    Hank,Tom
4    
5    Cruise,Tom

我需要将字符串拆分':'并删除','，需要反转名称并再次附加相同的数据:，最后数据应该如图所示

Id | ColA
----------
1    John Peter:Jack Ryan:Chris Evans
2    John Peter:Jack Ryan
3    Tom Hank
4    
5    Tom Cruise

请让我知道如何实现我尝试使用的这一点Replace，Substring但是如果我们的数据有些由两个冒号分隔，有些由一个冒号分隔，我们该怎么做。有什么方法可以识别和实现上述格式的数据。

标签： sqlsql-servertsqlsql-server-2008-r2

解决方案

这是 SQL Server 2008 及更高版本的解决方案。

它基于 XML 和 XQuery。

使用 XQuery 的 FLWOR 表达式可以标记奇数和偶数 XML 元素。其余的只是REPLACE()组成所需输出的几个函数调用。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(1024));
INSERT INTO @tbl (tokens) VALUES
('Peter,John:Ryan,Jack:Evans,Chris'),
('Peter,John:Ryan,Jack'),
('Hank,Tom'),
(''),
('Cruise,Tom');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ':'
    , @comma CHAR(1) = ',';

SELECT ID, tokens
   , REPLACE(REPLACE(c.query('
       for $x in /root/r[position() mod 2 eq 0]
       let $pos := count(root/r[. << $x]) 
       return concat($x, sql:variable("@comma"), (/root/r[$pos])[1])
       ').value('text()[1]', 'VARCHAR(8000)')
       , SPACE(1), @separator), @comma, SPACE(1)) AS result
FROM @tbl
    CROSS APPLY (SELECT CAST('<root><r><![CDATA[' + 
            REPLACE(REPLACE(tokens,@comma,@separator), @separator, ']]></r><r><![CDATA[') + 
            ']]></r></root>' AS XML)) AS t1(c)
ORDER BY ID;

输出

+----+----------------------------------+----------------------------------+
| ID |              tokens              |              result              |
+----+----------------------------------+----------------------------------+
|  1 | Peter,John:Ryan,Jack:Evans,Chris | John Peter:Jack Ryan:Chris Evans |
|  2 | Peter,John:Ryan,Jack             | John Peter:Jack Ryan             |
|  3 | Hank,Tom                         | Tom Hank                         |
|  4 |                                  | NULL                             |
|  5 | Cruise,Tom                       | Tom Cruise                       |
+----+----------------------------------+----------------------------------+

SQL #2（不要尝试，它不会工作）

不幸的是，SQL Server 甚至不完全支持 XQuery 1.0 标准。XQuery 3.1 是最新的标准。XQuery 1.0 功能fn:substring-after()和fn:substring-before()严重缺失。

在梦想的世界中，解决方案会简单得多，如下所示：

SELECT *
    , c.query('
    for $x in /root/r
    return concat(fn:substring-after($x, ","), ",", fn:substring-before($x, ","))
    ') 
FROM @tbl
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
            REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
            ']]></r></root>' AS XML)) AS t1(c);

请投票支持以下改进 SQL Server 的建议： SQL Server vNext（2019 年后）和 NoSQL 功能

它成为 SQL Server 最流行的请求之一。目前的投票总数为 590 并且还在增加中。

推荐阅读