(原创)SQL Server中几种特殊的“字符串到列表”的处理函数
普通的字符串分割函数已经见得很多了，写法性能也大不一样，通常都是直接基于字符串的处理，这里就不列出了。下面介绍几种特殊的方式1.使用xml
性能应该不如字符串处理快，没有具体测试过
IF OBJECT_ID('dbo.Split') IS NOT NULL
DROP FUNCTION dbo.Split
GO
CREATE FUNCTION dbo.Split(@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (data NVARCHAR(max))
AS
BEGIN
DECLARE @textXML XML;
SELECT @textXML = CAST('' + REPLACE(@data, @delimiter, '') + '' AS XML);
INSERT INTO @t(data)
SELECTT.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T(split)
RETURN
END
GO
DECLARE @text NVARCHAR(max)
SELECT @text = REPLICATE('ab,', 300) + 'ab'
SELECT * FROM dbo.Split(@text, ',')
2. 用xml作为参数
/**//* Assumes XML is as such
1
23
etc
Uses minimal xml markup to keep input size as small as possible
*/
ALTER FUNCTION dbo.fnXml2IntList(@xmlList xml)
RETURNS TABLE
AS
RETURN (SELECT tList.ListValue.value('.', 'int') AS ListValue
FROM @xmlList.nodes('list/i') AS tList(ListValue))
GO
使用示例：
SELECT * FROM dbo.fnXml2IntList
( '
1
23
'
)
3.利用笛卡尔集的方式
-- =============================================
-- Author: 代码乱了(靳如坦)
-- Create date: 2008-06-10
-- Description: 把以','分隔的字符串，转换成table
-- 示例 :select * from .('123,xxx,dfdsf,rer')
-- =============================================
create Function .(@strings varchar(2000))
Returns @ReturnTable Table(ID varchar(max))
As
Begin
Insert @ReturnTable
select substring(c,2,charindex(',',c,2)-2) as empno from
(
select substring(csv.emps,iter.pos,len(csv.emps)) as c from
(
select ','+@strings+',' as emps
) csv,
(
--select id as pos from t100
--生产1-10000的结果集
Selecta + b * 10 +c*100+d*1000+1 as pos From
(Select 0 As a Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 UnionSelect 7 Union Select 8 Union Select 9 ) A,
(Select 0 As b Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 UnionSelect 7 Union Select 8 Union Select 9 ) B,
(Select 0 As c Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 UnionSelect 7 Union Select 8 Union Select 9 ) c,
(Select 0 As d Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 UnionSelect 7 Union Select 8 Union Select 9 ) d
) iter where iter.pos1 and substring(c,1,1) = ','
Return
End
该函数主要运用了笛卡尔集的特点，速度上稍微慢于普通字符串处理，不过比较稳定，没有循环处理，结构上比较清晰。
需要注意到是，这个函数只能处理数组大小为10000的字符串，如果想处理更多。建议把上面的产生1-10000的结果集再扩大，并放在一个表内，这样性能肯定会有所提高，我这儿只是为了方便直接用内嵌视图，实际应用中应该先生成1-10000（根据实际应用可大可小）的数据表。如果大家发现什么问题，欢迎指正，谢谢。
4.运用CTE，性能应该不错，未测试性能
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
go
--Test
select * from dbo.DelimitedSplit8K('d,bb,22,11111',',')
页:
[1]