CREATE FUNCTION [dbo].[Split]
(
@origStr VARCHAR(8000) , --待拆分的字符串
@markStr VARCHAR(100)
) --拆分标记，如','
RETURNS @splittable TABLE
(
id INT NOT NULL , --编号ID
item INT NOT NULL --拆分后的字符串
)
AS
BEGIN
DECLARE @strlen INT ,
@postion INT ,
@start INT ,
@sublen INT ,
@TEMPstr VARCHAR(200) ,
@TEMPid INT
SELECT @strlen = LEN(@origStr) ,
@start = 1 ,
@sublen = 0 ,
@postion = 1 ,
@TEMPstr = '' ,
@TEMPid = 0
IF ( RIGHT(@origStr, 1) <> @markStr )
BEGIN
SET @origStr = @origStr + @markStr
END
WHILE ( ( @postion <= @strlen )
AND ( @postion != 0 )
)
BEGIN
IF ( CHARINDEX(@markStr, @origStr, @postion) != 0 )
BEGIN
SET @sublen = CHARINDEX(@markStr, @origStr, @postion)
- @postion ;
END
ELSE
BEGIN
SET @sublen = @strlen - @postion + 1 ;
END
IF ( @postion <= @strlen )
BEGIN
SET @TEMPid = @TEMPid + 1 ;
SET @TEMPstr = SUBSTRING(@origStr, @postion, @sublen) ;
INSERT INTO @splittable
( id, item )
VALUES ( @TEMPid, @TEMPstr )
IF ( CHARINDEX(@markStr, @origStr, @postion) != 0 )
BEGIN
SET @postion = CHARINDEX(@markStr, @origStr,
@postion) + 1
END
ELSE
BEGIN
SET @postion = @postion + 1
END
END
END
RETURN
END