SQL 分割符“|”

/*
StringToTable
*/
CREATE FUNCTION StringToTable(@StringX varchar(8000),@Split nvarchar(10))
RETURNS @TableResult TABLE(TableID nvarchar(20))
AS
BEGIN
DECLARE @Index int
DECLARE @LenIndex int
SELECT @LenIndex=LEN(@Split),@Index=CHARINDEX(@Split,@StringX,1)
WHILE (@Index>=1)
BEGIN
INSERT INTO @TableResult SELECT LEFT(@StringX,@Index-1)
SELECT

@StringX=RIGHT(@StringX,LEN(@StringX)-@Index-@LenIndex+1),@Index=CHARINDEX(@Split,@Stri

ngX,1)
END
IF(@StringX<>'') INSERT INTO @TableResult SELECT @StringX
RETURN
END

 

字符串分割自定义函数

declare @s1 varchar(8000),@s2 varchar(8000)
set @s1='1,2,3,5'
set @s2='1,2,3,4,5,6'

set @s1=' select id=' + replace(@s1,',',' union all select ')
set @s2=' select id=' + replace(@s2,',',' union all select ')

set @s1=' select b.* from (' + @s1 + ') a right join (' + @s2 + ') b on a.id=b.id where a.id is null '
exec(@s1)