在处理来自网站的数据时，经常会遇到需要解析多值字段的情况。例如，许多网页表单包含“选择所有适用项”的复选框，这类问题的数据通常以一个分隔的字符串形式记录在文本文件中或返回到数据库。例如，一组复选框的索引值可能被记录为一个由分隔符隔开的数字字符串：'1|3|6|11'。挑战在于如何将这些分隔的值转换为数据库用户可以理解的格式。需要将分隔的单个字段值分解并存为与父表中源行相关联的子表中的单独行。
在Web应用程序中预先处理这些数据是最佳方式。如果这不是一个选项，那么必须在导入数据库时处理。SQL Server提供了一个用户定义的函数（UDF），可以快速将分隔字符串中的数据转换为有意义的数据。
SQL Server用户定义函数（UDF）的一个有趣功能是能够返回一个表作为结果。这个UDF利用这一特性，将分隔字符串中的每个元素作为结果表中的一行返回。函数的结果可以像使用任何其他表一样使用。它可以包含在多表查询中，或者直接由函数调用返回。
以下是创建一个名为fn_ParseText2Table的函数的示例，该函数解析分隔字符串中的值，并将结果作为索引表返回。
CREATE FUNCTION [fn_ParseText2Table]
(
@p_SourceText VARCHAR(MAX),
@p_Delimeter VARCHAR(100) = ',' -- 默认为逗号分隔。
)
RETURNS @retTable TABLE
(
[Position] INT IDENTITY(1,1),
[Int_Value] INT,
[Num_Value] NUMERIC(18,3),
[Txt_Value] VARCHAR(MAX),
[Date_value] DATETIME
)
AS
BEGIN
DECLARE @w_xml xml;
SET @w_xml = N'
'
+ REPLACE(@p_SourceText, @p_Delimeter, '')
+ ' ';
INSERT INTO @retTable
([Int_Value], [Num_Value], [Txt_Value], [Date_value])
SELECT CASE
WHEN ISNUMERIC([i].value('.', 'VARCHAR(MAX)')) = 1
THEN CAST([i].value('.', 'VARCHAR(MAX)') AS NUMERIC)
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 [Date_value]
FROM @w_xml.nodes('/root/i') AS [Items]([i]);
RETURN;
END;
GO
这个函数使用了XML方法来解析字符串，这种方法更高效，并且能够更好地扩展。
以下是使用fn_ParseText2Table函数的示例，包括单字符分隔符和多字符分隔符的情况。
-- 单字符分隔符
SELECT * FROM dbo.fn_ParseText2Table(
'100|120|130.56|Yes|Cobalt Blue|2016-04-04|2000-06-17',
'|'
);
-- 多字符分隔符
SELECT * FROM dbo.fn_ParseText2Table(
'Red, White, and, Blue',
', '
);
这些示例展示了如何使用函数来解析包含数字、文本和日期值的字符串。
要处理多行数据，可以使用游标。以下是游标块中代码的示例，用于将解析的值从字符串插入子表作为行。
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 = ', ';
-- 游标块开始
INSERT #tmp_Child (parent_id, ColorSelection, SelOrder)
SELECT @parent_id, t.Txt_Value, t.Position
FROM dbo.fn_ParseText2Table(@ColorSelections, @delim) AS t;
-- 游标块结束
SELECT * FROM #tmp_child;
DROP TABLE #tmp_child;