sqlserver 中分隔字符串（split）函数

ALTER FUNCTION [ dbo ] . [ fun_splitIds ]
(
@str varchar ( max ) ,
@split varchar ( 10 )
)
/*
2010-8-23 : by jrt
调用示例：
SELECT * FROM [fun_splitIds]('ab##呵呵##234##课课练考虑','##')
*/
RETURNS @t TABLE ( v varchar ( max ) )
AS
BEGIN
DECLARE @XmlIDs XML

SET @xmlIDs = ' <IDs>
<ID> ' + REPLACE ( @str , @split , ' </ID><ID> ' ) + ' </ID> ' +
' </IDs> '
INSERT INTO @t
SELECT x.v.value( ' . ' , ' varchar(max) ' )
FROM @xmlIDs .nodes( ' /IDs/ID ' ) x(v)

RETURN
END

转载于:https://www.cnblogs.com/SunsetAndTea/archive/2011/03/08/1977177.html