SQL 用户定义函数，用于解析分隔字符串
SQL 函数用于解析分隔符字符串并将其作为表返回。处理多字符分隔符并将结果作为表返回。
引言
已更新！
自从我发布它以来，我没有太关注它，但根据收到的几条请求，以及 SQL Server 中新增的功能，我认为它值得快速更新一下。
特别是，添加了对日期值的测试以及 DATETIME 列。 此外，还添加了一个使用 SQL Server 中的 XML 函数来解析字符串的版本。 XML 方法更高效且可扩展性更好。
祝好！
Clayton
处理从网站捕获的数据时，经常遇到的挑战之一是解析多值字段。 许多网络表单都有“选择所有适用项”复选框表单，此类问题的答案通常记录为单个分隔符字段值。
例如，复选框块的索引值可能会记录到文本文件中，或者作为分隔符数字字符串返回到数据库：'1|3|6|11'
挑战是将这些分隔符值获取到对数据库用户有意义的格式中。 单个分隔符值字段必须被分解并存储为父表中源行的子表中的单独行。
最好的方法是在 Web 应用程序中预先完成。 如果这不可行，则必须在导入到数据库时处理。 此 SQL Server 函数为您提供了一种将陷在分隔符字符串中的数据转换为有意义数据的方法。
SQL Server 用户自定义函数 (UDF) 的一个更有趣的功能是能够将表作为结果返回。 此 UDF 使用此功能将分隔符字符串中的每个元素作为结果表中的一行返回。 函数的结果可以像任何其他表一样使用。 它可以包含在多表查询中，或者由函数调用直接返回。
代码
CREATE FUNCTION [fn_ParseText2Table]
(@p_SourceText VARCHAR(MAX)
,@p_Delimeter VARCHAR(100)=',' --default to comma delimited.
)
RETURNS @retTable
TABLE([Position] INT IDENTITY(1,1)
,[Int_Value] INT
,[Num_Value] NUMERIC(18,3)
,[Txt_Value] VARCHAR(MAX)
,[Date_value] DATETIME
)
AS
/*
********************************************************************************
Purpose: Parse values from a delimited string
& return the result as an indexed table
Copyright 1996, 1997, 2000, 2003 Clayton Groom (<A href="mailto:Clayton_Groom@hotmail.com">Clayton_Groom@hotmail.com</A>)
Posted to the public domain Aug, 2004
2003-06-17 Rewritten as SQL 2000 function.
Reworked to allow for delimiters > 1 character in length
and to convert Text values to numbers
2016-04-05 Added logic for date values based on "new" ISDATE() function, Updated to use XML approach, which is more efficient.
********************************************************************************
*/
BEGIN
DECLARE @w_xml xml;
SET @w_xml = N'<root><i>' + replace(@p_SourceText, @p_Delimeter,'</i><i>') + '</i></root>';
INSERT INTO @retTable
([Int_Value]
, [Num_Value]
, [Txt_Value]
, [Date_value]
)
SELECT CASE
WHEN ISNUMERIC([i].value('.', 'VARCHAR(MAX)')) = 1
THEN CAST(CAST([i].value('.', 'VARCHAR(MAX)') AS NUMERIC) AS INT)
END AS [Int_Value]
, CASE
WHEN ISNUMERIC([i].value('.', 'VARCHAR(MAX)')) = 1
THEN CAST([i].value('.', 'VARCHAR(MAX)') AS NUMERIC(18, 3))
END AS [Num_Value]
, [i].value('.', 'VARCHAR(MAX)') AS [txt_Value]
, CASE
WHEN ISDATE([i].value('.', 'VARCHAR(MAX)')) = 1
THEN CAST([i].value('.', 'VARCHAR(MAX)') AS DATETIME)
END AS [Num_Value]
FROM @w_xml.nodes('//root/i') AS [Items]([i]);
RETURN;
END;
GO
--Old version, with some updates...
CREATE FUNCTION [fn_ParseText2Table_old]
(
@p_SourceText VARCHAR(MAX)
, @p_Delimeter VARCHAR(100) = ',' --default to comma delimited.
)
RETURNS @retTable TABLE
(
[Position] INT IDENTITY(1, 1)
, [Int_Value] INT
, [Num_Value] NUMERIC(18, 3)
, [Txt_Value] VARCHAR(MAX)
, [Date_value] DATETIME
)
AS
/*
********************************************************************************
Purpose: Parse values from a delimited string
& return the result as an indexed table
Copyright 1996, 1997, 2000, 2003 Clayton Groom (<A href="mailto:Clayton_Groom@hotmail.com">Clayton_Groom@hotmail.com</A>)
Posted to the public domain Aug, 2004
2003-06-17 Rewritten as SQL 2000 function.
Reworked to allow for delimiters > 1 character in length
and to convert Text values to numbers
2016-04-05 Added logic for date values based on "new" ISDATE() function
********************************************************************************
*/
BEGIN
DECLARE @w_Continue INT
, @w_StartPos INT
, @w_Length INT
, @w_Delimeter_pos INT
, @w_tmp_int INT
, @w_tmp_num NUMERIC(18, 3)
, @w_tmp_txt VARCHAR(MAX)
, @w_date DATETIME
, @w_Delimeter_Len TINYINT;
IF LEN(@p_SourceText) = 0
BEGIN
SET @w_Continue = 0; -- force early exit
END;
ELSE
BEGIN
-- parse the original @p_SourceText array into a temp table
SET @w_Continue = 1;
SET @w_StartPos = 1;
SET @p_SourceText = RTRIM(LTRIM(@p_SourceText));
SET @w_Length = DATALENGTH(RTRIM(LTRIM(@p_SourceText)));
SET @w_Delimeter_Len = LEN(@p_Delimeter);
END;
WHILE @w_Continue = 1
BEGIN
SET @w_Delimeter_pos = CHARINDEX(@p_Delimeter, (SUBSTRING(@p_SourceText, @w_StartPos, ((@w_Length-@w_StartPos)+@w_Delimeter_Len))));
IF @w_Delimeter_pos > 0 -- delimeter(s) found, get the value
BEGIN
SET @w_tmp_txt = LTRIM(RTRIM(SUBSTRING(@p_SourceText, @w_StartPos, (@w_Delimeter_pos-1))));
IF ISNUMERIC(@w_tmp_txt) = 1
BEGIN
SET @w_tmp_int = CAST(CAST(@w_tmp_txt AS NUMERIC) AS INT);
SET @w_tmp_num = CAST(@w_tmp_txt AS NUMERIC(18, 3));
END;
ELSE
BEGIN
SET @w_tmp_int = NULL;
SET @w_tmp_num = NULL;
END;
IF ISDATE(@w_tmp_txt) = 1
BEGIN
SET @w_date = CAST(@w_tmp_txt AS DATETIME);
END;
ELSE
BEGIN
SET @w_date = NULL;
END;
SET @w_StartPos = @w_Delimeter_pos + @w_StartPos + (@w_Delimeter_Len - 1);
END;
ELSE -- No more delimeters, get last value
BEGIN
SET @w_tmp_txt = LTRIM(RTRIM(SUBSTRING(@p_SourceText, @w_StartPos, ((@w_Length-@w_StartPos)+@w_Delimeter_Len))));
IF ISNUMERIC(@w_tmp_txt) = 1
BEGIN
SET @w_tmp_int = CAST(CAST(@w_tmp_txt AS NUMERIC) AS INT);
SET @w_tmp_num = CAST(@w_tmp_txt AS NUMERIC(18, 3));
END;
ELSE
BEGIN
SET @w_tmp_int = NULL;
SET @w_tmp_num = NULL;
END;
IF ISDATE(@w_tmp_txt) = 1
BEGIN
SET @w_date = CAST(@w_tmp_txt AS DATETIME);
END;
ELSE
BEGIN
SET @w_date = NULL;
END;
SELECT @w_Continue = 0;
END;
INSERT INTO @retTable
VALUES
(@w_tmp_int
, @w_tmp_num
, @w_tmp_txt
, @w_date
);
END;
RETURN;
END;
GO
使用示例
单字符分隔符
select * from dbo.fn_ParseText2Table('100|120|130.56|Yes|Cobalt Blue|2016-04-04|2000-06-17','|')
/*
Position Int_Value Num_value txt_value Date_value
----------- ----------- -------------------- ----------- -----------------------
1 100 100.000 100
2 120 120.000 120
3 131 130.560 130.56
4 NULL NULL Yes
5 NULL NULL Cobalt Blue
6 NULL NULL NULL 2016-04-04 00:00:00.000
7 NULL NULL NULL 2000-06-17 00:00:00.000
*/
多字符分隔符
select * from dbo.fn_ParseText2Table('Red, White, and, Blue',', ')
/*
Position Int_Value Num_value txt_value Date_value
----------- ----------- -------------------- ---------- ----------
1 NULL NULL Red NULL
2 NULL NULL White NULL
3 NULL NULL and NULL
4 NULL NULL Blue NULL
*/
大型多字符分隔符
select * from dbo.fn_ParseText2Table('Red<Tagname>White<Tagname>Blue','<Tagname>')
/*
Position Int_Value Num_value txt_value Date_value
----------- ----------- -------------------- ---------- ----------
1 NULL NULL Red NULL
2 NULL NULL White NULL
3 NULL NULL and NULL
4 NULL NULL Blue NULL
*/
不幸的是，使用游标是处理多行数据的唯一方法。
以下是游标块内的代码示例，用于将解析后的值从字符串插入到子表中的行：
作为插入语句中的表
create table #tmp_Child (parent_id int, ColorSelection varchar(30), SelOrder tinyint)
declare @parent_id int
,@ColorSelections varchar(255)
,@delim varchar(100)
set @parent_id = 122
set @ColorSelections = 'Red, White, and, Blue'
set @delim = ', '
-- cursor block starts here
insert #tmp_Child (parent_id, ColorSelection, SelOrder)
select @parent_id
,t.txt_value
,t.position
from dbo.fn_ParseText2Table(@ColorSelections, @delim) as t
-- cursor block ends here
select * from #tmp_child
drop table #tmp_child