利用MSSQL解析复杂字符串分组

1. 利用MSSQL解析复杂字符串分组

在SQL Server中，如果要对字符串进行分隔并聚合，有多种方法可以实现。例如，使用特定字符进行字符串拆分，以及使用常规表达式和XML。在本篇文章中，我们将解析一个复杂的字符串，并对其进行分组。

1.1 需求场景

假设我们有一个表，其中包含一个名为'Names'的列，其中存储了逗号分隔的名称列表。例如，以下是一个名为'TableA'的表及其内容：

CREATE TABLE TableA (ID INT, Names VARCHAR(100))
INSERT INTO TableA VALUES (1, 'John,Smith,Sam')
INSERT INTO TableA VALUES (2, 'Tom,Mike')

现在我们想要将这些名称按照第一个字符分组，并将每个组中的名称以逗号分隔的形式列出。例如，上面的数据将被分成以下两组：

J: John
S: Smith, Sam
T: Tom
M: Mike

下面，我们就来一步一步地实现这一需求。

1.2 利用XML方式分隔字符串

首先，我们将使用XML函数“nodes”分隔逗号分隔的字符串。以下是一个使用nodes函数的示例：

SELECT 
    id = RowNumber,
    Split.a.value('.', 'VARCHAR(100)') AS Names 
FROM
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber, 
        CAST ('' + REPLACE(Names, ',', '') + '' AS XML) AS Name 
    FROM 
        TableA
) AS A 
CROSS APPLY 
    Name.nodes ('/M') AS Split(a)

这个查询将结果变成以下形式：

id Names
-- -----
1  John
1  Smith
1  Sam
2  Tom
2  Mike

现在我们已经成功拆分了逗号分隔的字符串，我们需要对其按照第一个字符进行分组。接下来，我们将使用“SUBSTRING”函数来提取第一个字符。

1.3 分组聚合字符串

为了按照第一个字符分组并聚合每个组中的名称，我们需要使用GROUP BY，并使用“FOR XML PATH('')”来连接名称。以下是实现分组聚合的完整查询：

SELECT     
    SUBSTRING(SplitNames.Names, 1, 1) AS GroupChar, 
    Names = STUFF((SELECT ', ' + SplitNamesInner.Names 
                   FROM 
                        (SELECT 
                             Split.a.value('.', 'VARCHAR(100)') AS Names 
                         FROM 
                             (SELECT CAST ('' + REPLACE(Names, ',', '') + '' AS XML) AS Name 
                              FROM TableA) AS A 
                         CROSS APPLY 
                             Name.nodes ('/M') AS Split(a)) AS SplitNamesInner 
                   WHERE 
                        SUBSTRING(SplitNamesInner.Names, 1, 1) = SUBSTRING(SplitNames.Names, 1, 1) 
                   FOR XML PATH('')), 1, 2, '') 
FROM 
    (SELECT DISTINCT 
         SUBSTRING(Split.a.value('.', 'VARCHAR(100)'), 1, 1) AS Names 
     FROM 
         (SELECT CAST ('' + REPLACE(Names, ',', '') + '' AS XML) AS Name 
          FROM TableA) AS A 
     CROSS APPLY 
         Name.nodes ('/M') AS Split(a)) AS SplitNames

有了这个查询，我们可以成功地按照第一个字符分组并聚合每个组中的名称：

GroupChar Names
-------- ---------------
J        John, Sam, Smith
M        Mike
S        Smith
T        Tom

2. 总结

在SQL Server中，可以使用各种方法对字符串进行拆分和聚合。在本文中，我们演示了如何使用XML函数和GROUP BY将逗号分隔的字符串按照第一个字符分组，并聚合每个组中的名称。希望这篇文章对你有所帮助。