带有增强功能的自定义字符串拆分函数
本文介绍了一个自定义的 SQL Server 函数，用于根据字符或一组字符拆分字符串。该函数还包含一些方便的增强功能。
目录
引言
首先，如果您正在寻找一个拆分字符串的函数，并且您使用的是 SQL Server 2016 或更高版本，请考虑使用 STRING_SPLIT (Transact-SQL)。
最近在回答一个问题时，我编写了一个小的函数来根据空格拆分字符串。为了保持答案的简单性，我决定使用递归 CTE（公共表表达式）进行拆分。通过对答案进行一些增强，该函数可以在多种情况下使用，例如从句子中查找单个单词或将一行 CSV 数据拆分成多行。
此处提供的函数包含以下功能
- 根据分隔符将字符串拆分成一组行
- 为找到的每个项提供序数值
- 如果它们之间没有数据，则能够删除连续的分隔符
- 删除所有不希望出现的字符
- 去除重复项的功能
- 如有必要，重新计算剩余值的序数
- 对重复项执行不区分大小写的比较
CustomStringSplit 函数
完整的函数如下所示
----------------------------------
-- CustomStringSplit
----------------------------------
CREATE FUNCTION CustomStringSplit(@value nvarchar(max),
@delimiter nvarchar(max),
@removeDuplicateDelimiters bit = 0,
@removeChars nvarchar(100) = NULL,
@suppressDuplicates bit = 0,
@recalculateOrdinals bit = 0,
@lowerCaseComparison bit = 0)
RETURNS @items TABLE (
Ordinal int,
Part nvarchar(max)
) AS
BEGIN
DECLARE @charCounter int;
-- Remove duplicate delimiters
IF @removeDuplicateDelimiters = 1 BEGIN
WHILE CHARINDEX(REPLICATE(@delimiter, 2), @value) > 0 BEGIN
SET @value = REPLACE(@value, REPLICATE(@delimiter, 2), @delimiter);
END;
END;
-- Remove unwanted characters
IF @removeChars IS NOT NULL BEGIN
SET @charCounter = 1;
WHILE @charCounter <= LEN(@removeChars) BEGIN
SET @value = REPLACE(@value, SUBSTRING(@removeChars, @charCounter, 1), '');
SET @charCounter = @charCounter + 1;
END;
END;
-- Split the data into separate rows using a recursive CTE
WITH Items (Ordinal, Part, Remainder) AS (
SELECT 1 AS Ordinal,
CASE
WHEN CHARINDEX(@delimiter, @value) > 0
THEN LEFT(@value, CHARINDEX(@delimiter, @value) - 1)
ELSE @value
END AS Part,
CASE
WHEN CHARINDEX(@delimiter, @value) > 0
THEN SUBSTRING(@value,
CHARINDEX(@delimiter, @value) + DATALENGTH(@delimiter) / 2,
99999999)
ELSE NULL
END AS Remainder
UNION ALL
SELECT Items.Ordinal + 1 AS Ordinal,
CASE
WHEN CHARINDEX(@delimiter, Remainder) > 0
THEN LEFT(Remainder, CHARINDEX(@delimiter, Remainder) - 1)
ELSE Remainder
END AS Part,
CASE
WHEN CHARINDEX(@delimiter, Remainder) > 0
THEN SUBSTRING(Remainder,
CHARINDEX(@delimiter, Remainder) + DATALENGTH(@delimiter) / 2,
99999999)
ELSE NULL
END AS Remainder
FROM Items
WHERE Items.Remainder IS NOT NULL
)
INSERT INTO @items (Ordinal, Part)
SELECT Items.Ordinal, Items.Part
FROM Items
OPTION (MAXRECURSION 32767);
-- Remove duplicate values
IF @suppressDuplicates = 1 BEGIN
DELETE i FROM @items AS i
WHERE EXISTS (SELECT 1
FROM @items i2
WHERE CASE @lowerCaseComparison
WHEN 1 THEN LOWER(i2.Part)
ELSE i2.Part
END = CASE @lowerCaseComparison
WHEN 1 THEN LOWER(i.Part)
ELSE i.Part
END
AND i2.Ordinal < i.Ordinal);
-- Recalculate ordinals
IF @recalculateOrdinals = 1 BEGIN
UPDATE i
SET Ordinal = (SELECT COUNT(*)
FROM @items i2
WHERE i2.Ordinal <= i.Ordinal)
FROM @items i;
END;
END;
RETURN;
END;
首先，它返回一个表，因此从字符串中找到的单个项将作为结果集（一个表）返回。这种函数是表值函数 (TVF)。参数是
@value，要拆分的字符串值@delimiter，拆分数据时使用的分隔符。分隔符可以是一个单个字符，也可以是一个单词，例如@removeDuplicateDelimiters，如果它们之间没有数据，是否应删除连续的分隔符？
值可以是0（false）或1（true），默认为0（false）。@removeChars，应从结果中删除哪些字符。如果字符串包含多个字符，则每个字符都会被单独从字符串中删除。
默认为NULL。@suppressDuplicates，是否应从结果集中删除重复值？
值可以是0（false）或1（true），默认为0（false）。@recalculateOrdinals，如果删除了重复值，是否应保留第一个出现的原始序数，还是应重新计算序数编号？
值可以是0（false）或1（true），默认为0（false）。@lowerCaseComparison，删除重复项时，是应使用小写（不区分大小写）还是按原样进行比较。仅当数据库中的比较默认区分大小时，此选项才有意义。
值可以是0（false）或1（true），默认为0（false）。
代码的一些说明
-- Remove duplicate delimiters
IF @removeDuplicateDelimiters = 1 BEGIN
WHILE CHARINDEX(REPLICATE(@delimiter, 2), @value) > 0 BEGIN
SET @value = REPLACE(@value, REPLICATE(@delimiter, 2), @delimiter);
END;
END;
上面的代码尝试查找重复的分隔符。例如，如果分隔符是 ',' ，代码会重复分隔符两次，得到 ',,' ，并用单个分隔符替换所有出现。替换是在循环内完成的，因为 CHARINDEX 函数只对字符串进行一次向前扫描，因此如果分隔符在原始字符串中出现次数超过两次，则需要多次传递。
-- Remove unwanted characters
IF @removeChars IS NOT NULL BEGIN
SET @charCounter = 1;
WHILE @charCounter <= LEN(@removeChars) BEGIN
SET @value = REPLACE(@value, SUBSTRING(@removeChars, @charCounter, 1), '');
SET @charCounter = @charCounter + 1;
END;
END;
通过循环遍历单个字符并将所有出现替换为空值来删除不需要的字符。换句话说，删除它们。
-- Split the data into separate rows using a recursive CTE
WITH Items (Ordinal, Part, Remainder) AS (
SELECT 1 AS Ordinal,
CASE
WHEN CHARINDEX(@delimiter, @value) > 0
THEN LEFT(@value, CHARINDEX(@delimiter, @value) - 1)
ELSE @value
END AS Part,
CASE
WHEN CHARINDEX(@delimiter, @value) > 0
THEN SUBSTRING(@value,
CHARINDEX(@delimiter, @value) + DATALENGTH(@delimiter) / 2,
99999999)
ELSE NULL
END AS Remainder
UNION ALL
SELECT Items.Ordinal + 1 AS Ordinal,
CASE
WHEN CHARINDEX(@delimiter, Remainder) > 0
THEN LEFT(Remainder, CHARINDEX(@delimiter, Remainder) - 1)
ELSE Remainder
END AS Part,
CASE
WHEN CHARINDEX(@delimiter, Remainder) > 0
THEN SUBSTRING(Remainder,
CHARINDEX(@delimiter, Remainder) + DATALENGTH(@delimiter) / 2,
99999999)
ELSE NULL
END AS Remainder
FROM Items
WHERE Items.Remainder IS NOT NULL
)
INSERT INTO @items (Ordinal, Part)
SELECT Items.Ordinal, Items.Part
FROM Items
OPTION (MAXRECURSION 32767);
这是主要部分。公共表表达式使用三个列
Ordinal，每次传递将序数增加一，从而产生递增的行编号Part，文本中下一个分隔符之前的所有数据Remainder，下一个分隔符之后文本的剩余部分
例如，如果文本是 'This is a sentence.'，分隔符是单个空格，则查询将返回以下行。
Ordinal Part Remainder
------- --------- ----------------
1 This is a sentence.
2 is a sentence.
3 a sentence.
4 sentence. NULL
递归一直持续到 Remainder 中的值为 NULL，然后将结果返回给调用语句。在这种情况下，语句是一个 INSERT 语句，将数据添加到将在最后返回给调用者的表变量中。
创建此函数时，一个障碍是我尝试使用 LEN 函数。然而，LEN 会自动删除尾随空格，因此在此场景中不可用。将 LEN 更改为 DATALENGTH 后，返回值必须除以 2 ，因为该函数返回使用的字节数。对于 nvarchar ，每个字符使用两个字节。
DELETE i FROM @items AS i
WHERE EXISTS (SELECT 1
FROM @items i2
WHERE CASE @lowerCaseComparison
WHEN 1 THEN LOWER(i2.Part)
ELSE i2.Part
END = CASE @lowerCaseComparison
WHEN 1 THEN LOWER(i.Part)
ELSE i.Part
END
AND i2.Ordinal < i.Ordinal);
此语句删除重复项，可以使用数据库中的默认比较，也可以通过比较小写值来进行不区分大小写的比较（如果需要）。
有些地方可能看起来有点奇怪
- 表变量的表别名。由于在相关的
EXISTS条件中引用了表变量的行，因此需要一个表别名。定义别名的语法是
DELETE <table alias> FROM <table variable> AS <table alias> EXISTS中的比较。WHERE子句可以包含CASE语句。通常，出于性能原因，应避免使用它们，但在这种情况下，我认为在大多数情况下，行数非常少，性能损失并不显著。但是，如果这导致问题，这一条语句可以轻松地分成两条单独的语句。
-- Recalculate ordinals
IF @recalculateOrdinals = 1 BEGIN
UPDATE i
SET Ordinal = (SELECT COUNT(*)
FROM @items i2
WHERE i2.Ordinal <= i.Ordinal)
FROM @items i;
END;
最后一部分是重新计算剩余项的序数。如果删除了重复值但未重新计算，则结果集将包含每个项第一次出现的序数。
一些测试
分隔单词
让我们用这个函数运行一些测试。首先，我们将使用一个短句来演示功能。考虑以下代码
DECLARE @repeating varchar(max);
SET @repeating = 'You cannot end a sentence with because, because because is a conjunction.';
SELECT * FROM CustomStringSplit(@repeating, ' ', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
结果是：
Ordinal Part
------- ----------------
1 You
2
3
4 cannot
5
6
7 end
8 a
9 sentence
10 with
11 because,
12 because
13 because
14 is
15 a
16 conjunction.
因此，整个字符串被分割成由单个空格分隔的各个项。现在，如果您想删除没有数据填充的重复分隔符，并删除句点和逗号，调用将如下所示
SELECT * FROM CustomStringSplit(@repeating, ' ', 1 , '.,', DEFAULT, DEFAULT, DEFAULT);
结果将是
Ordinal Part
------- ----------------
1 You
2 cannot
3 end
4 a
5 sentence
6 with
7 because
8 because
9 because
10 is
11 a
12 conjunction
现在我们有了句子中的所有单词。现在，如果您希望结果只包含每个单词一次，调用将是，例如
SELECT * FROM CustomStringSplit(@repeating, ' ', 1 , '.,', 1, 0, 0);
现在结果是
Ordinal Part
------- -------------------
1 You
2 cannot
3 end
4 a
5 sentence
6 with
7 because
10 is
12 conjunction
请注意，虽然只返回了九行，但序数编号一直到 12 ，显示每个单词的原始位置。如果您希望重新计算序数编号，调用将是
SELECT * FROM CustomStringSplit(@repeating, ' ', 1 , '.,', 1, 1, 0);
现在，结果是
Ordinal Part
------- -------------------
1 You
2 cannot
3 end
4 a
5 sentence
6 with
7 because
8 is
9 conjunction
使用单词作为分隔符
为了进行不同类型的测试，让我们使用一个单词作为分隔符。考虑以下情况
DECLARE @repeating varchar(max);
SET @repeating = 'This is first part and then the second and and '
+ 'after that comes the third and finally the fourth.';
SELECT *
FROM CustomStringSplit(@repeating, ' and ', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
上面的调用会将句子拆分成五个部分。所以结果是
Ordinal Part
------- -------------------
1 This is first part
2 then the second
3
4 after that comes the third
5 finally the fourth.
拆分单行 CSV 数据
拆分函数通常用于将 CSV（逗号分隔值）数据拆分成单个项。您可以尝试以下方法
DECLARE @singleCsvRow varchar(max);
SET @singleCsvRow = '1,2,3,''A B'',''C D'',1';
SELECT *
FROM CustomStringSplit(@singleCsvRow, ',', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
上面的调用将导致
Ordinal Part
------- -------------------
1 1
2 2
3 3
4 'A B'
5 'C D'
6 1
如果您想从文本数据中删除单引号，调用将是
SELECT * FROM CustomStringSplit(@singleCsvRow, ',', 1 , '''', DEFAULT, DEFAULT, DEFAULT);
结果几乎相同，只是没有单引号
Ordinal Part
------- -------------------
1 1
2 2
3 3
4 A B
5 C D
6 1
CustomStringTableSplit 函数
为了进一步处理 CSV 处理，前面介绍的函数可以拆分单个 CSV 行。然而，通常的要求是将所有 CSV 行，例如从一个文件中进行拆分。为了做到这一点，让我们创建一个小的实用函数来处理多行。
首先，我们需要一个类型，可用于参数定义
----------------------------------
-- StringItems type
----------------------------------
CREATE TYPE StringItems AS TABLE (
StringItem nvarchar(max)
);
然后是函数
----------------------------------
-- CustomStringTableSplit
----------------------------------
CREATE FUNCTION CustomStringTableSplit(@values StringItems READONLY,
@delimiter nvarchar(max),
@removeDuplicateDelimiters bit = 0,
@removeChars nvarchar(100) = NULL,
@suppressDuplicates bit = 0,
@recalculateOrdinals bit = 0,
@lowerCaseComparison bit = 0)
RETURNS @items TABLE (
RowNumber int,
Ordinal int,
Part nvarchar(max)
) AS
BEGIN
DECLARE @rowNumber int;
DECLARE @value nvarchar(max);
DECLARE valueCursor CURSOR FOR SELECT StringItem
FROM @values;
OPEN valueCursor;
FETCH NEXT FROM valueCursor INTO @value;
SET @rowNumber = 1;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @items
SELECT @rowNumber,
i.Ordinal,
i.Part
FROM CustomStringSplit(@value,
@delimiter,
@removeDuplicateDelimiters,
@removeChars,
@suppressDuplicates,
@recalculateOrdinals,
@lowerCaseComparison) i;
FETCH NEXT FROM valueCursor INTO @value;
SET @rowNumber = @rowNumber + 1;
END;
CLOSE valueCursor;
DEALLOCATE valueCursor;
RETURN;
END;
上面的函数基本上接收与前一个函数相同的参数。但是，这次，值不是单行，而是使用前面定义的类型的一组行。
该代码为作为参数传递的表创建了一个游标，当遍历数据时，所有从 CustomStringSplit 返回的行都将被追加到结果集中。CustomStringTableSplit 的结果集包含与前一个函数相同的列，但还有一个额外的列 RowNumber ，该列定义了从中提取返回行的原始数据中的行号。
测试 CustomStringTableSplit
现在让我们用 CSV 数据运行几个测试
DECLARE @items StringItems
INSERT INTO @items VALUES
('1,2,3,4,5'),
('''A'',''B'',''C'',,''E'''),
('4,5'),
('a,B,c');
SELECT * FROM CustomStringTableSplit(@items, ',', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
上面调用的结果将是
RowNumber Ordinal Part
--------- ------- -----------
1 1 1
1 2 2
1 3 3
1 4 4
1 5 5
2 1 'A'
2 2 'B'
2 3 'C'
2 4
2 5 'E'
3 1 4
3 2 5
4 1 a
4 2 B
4 3 c
同样，如果我们想删除单引号，它将是
SELECT * FROM CustomStringTableSplit(@items, ',', 0, '''', 0, 0, 0);
现在结果是
RowNumber Ordinal Part
--------- ------- -----------
1 1 1
1 2 2
1 3 3
1 4 4
1 5 5
2 1 A
2 2 B
2 3 C
2 4
2 5 E
3 1 4
3 2 5
4 1 a
4 2 B
4 3 c
因此，这种函数易于使用，可以将完整的 CSV 文件解析到包含原始行和列信息的结果集中。
确保所有行都包含相同数量的列
另一个例子是检查 CSV 数据是否为所有行包含相同数量的列。查看前一个示例中的测试数据，行包含以下列
- 第 1 行，五列
- 第 2 行，五列（一列为空）
- 第 3 行，两列
- 第 4 行，三列
我们可以使用一个小的 CTE 来获取每行的列数。考虑以下
DECLARE @items StringItems
INSERT INTO @items VALUES
('1,2,3,4,5'),
('''A'',''B'',''C'',,''E'''),
('4,5'),
('a,B,c');
WITH RowColumns (RowNumber, RowMax) AS (
SELECT a.RowNumber, MAX(a.Ordinal)
FROM CustomStringTableSplit(@items, ',', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) a
GROUP BY a.RowNumber
)
SELECT * FROM RowColumns;
结果是：
RowNumber RowMax
--------- ------
1 5
2 5
3 2
4 3
正如您所见，有不同数量的列。所以如果我们只想知道所有行是否包含相同数量的行，查询可能是
WITH RowColumns (RowNumber, RowMax) AS (
SELECT a.RowNumber, MAX(a.Ordinal)
FROM CustomStringTableSplit(@items, ',', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) a
GROUP BY a.RowNumber
)
SELECT CASE (SELECT COUNT(DISTINCT RowMax) FROM RowColumns)
WHEN 1 THEN 'All of the rows contain same amount of columns'
ELSE 'The amount of columns is different between rows'
END;
现在结果是
The amount of columns is different between rows
历史
- 2018 年 12 月 26 日：创建