/****** Description: select * from dbo.Split('100,200 300;400',', ;')
return value is 4 rows table with values 100,200,300,400.
******/
CREATE FUNCTION [dbo].[Split]
(
@str AS NVARCHAR(1000) ,
@delimiters AS NVARCHAR(100)
)
RETURNS @t TABLE
(
pos INT NOT NULL ,
value NVARCHAR(100) NOT NULL
)
AS
BEGIN
DECLARE @end INT ,
@start INT ,
@pos INT ,
@deliNO INT ,
@strChar NVARCHAR(1) ,
@delimiter NVARCHAR(1)
SELECT @str=REPLACE(@str,' ',' '),@delimiters=REPLACE(@delimiters,' ',' ')
SET @str = LTRIM(RTRIM(@str))+SUBSTRING(@delimiters, 1, 1)
SELECT @pos = 1,@start = 1,@end = 1
WHILE @end <= LEN(@str)
BEGIN
SELECT @strChar = SUBSTRING(@str, @end, 1) ,
@deliNO = 1
WHILE @deliNO <= LEN(@delimiters)
BEGIN
SET @delimiter = SUBSTRING(@delimiters, @deliNO, 1)
IF @strChar = @delimiter
BEGIN
IF SUBSTRING(@str, @start, @end - @start) <> ''
BEGIN
INSERT INTO @t
VALUES ( @pos,SUBSTRING(@str, @start,@end - @start) )
SET @pos = @pos + 1
END
SET @start = @end + 1
break
END
SET @deliNO = @deliNO + 1
END
SET @end = @end + 1
END
RETURN
END
GO
