SPLIT Function
CREATE FUNCTION [DBO].[SPLITDELIMITED]
(
@LIST NVARCHAR(2000),
@SPLITON NVARCHAR(1)
)
RETURNS @RTNVALUE TABLE (
ID INT IDENTITY(1,1),
VALUE NVARCHAR(100)
)
AS
BEGIN
WHILE (CHARINDEX(@SPLITON,@LIST)>0)
BEGIN
INSERT INTO @RTNVALUE (VALUE)
SELECT
VALUE = LTRIM(RTRIM(SUBSTRING(@LIST,1,CHARINDEX(@SPLITON,@LIST)-1)))
SET @LIST = SUBSTRING(@LIST,CHARINDEX(@SPLITON,@LIST)+LEN(@SPLITON),LEN(@LIST))
END
INSERT INTO @RTNVALUE (VALUE)
SELECT VALUE = LTRIM(RTRIM(@LIST))
RETURN
END
(
@LIST NVARCHAR(2000),
@SPLITON NVARCHAR(1)
)
RETURNS @RTNVALUE TABLE (
ID INT IDENTITY(1,1),
VALUE NVARCHAR(100)
)
AS
BEGIN
WHILE (CHARINDEX(@SPLITON,@LIST)>0)
BEGIN
INSERT INTO @RTNVALUE (VALUE)
SELECT
VALUE = LTRIM(RTRIM(SUBSTRING(@LIST,1,CHARINDEX(@SPLITON,@LIST)-1)))
SET @LIST = SUBSTRING(@LIST,CHARINDEX(@SPLITON,@LIST)+LEN(@SPLITON),LEN(@LIST))
END
INSERT INTO @RTNVALUE (VALUE)
SELECT VALUE = LTRIM(RTRIM(@LIST))
RETURN
END
Comments