CREATE FUNCTION FN_SplitSTRING (@Text nVarchar(max), @Delimeter nVarchar(50))
RETURNS @Table Table (Item Varchar(200))
/*
Written By: Nitish Kumar.
Objective: To split string with delimeter.
Written On: 28-July-2015.
SELECT * FROM FN_SplitSTRING('1,2,3,5,6,4,8',',')
SELECT * FROM FN_SplitSTRING('NITISH, KUMAR, JHA, FROM, SAMASTIPUR',',')
SELECT * FROM FN_SplitSTRING('17*858*858*8569*89*58*nitish','*')
*/
AS
BEGIN
DECLARE @i INT= LEN(@Text)
WHILE @i <> 0
BEGIN
INSERT INTO @Table SELECT LTRIM(RTRIM(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text))))
SET @Text= RIGHT(@Text,LEN(@Text) - LEN(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text)+1)) )
IF(LEN(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text)+1)) = 0)
BEGIN
INSERT INTO @Table SELECT LTRIM(@Text)
SET @i= 1;
END
SET @i= @i-1
END
RETURN
END
GO
RETURNS @Table Table (Item Varchar(200))
/*
Written By: Nitish Kumar.
Objective: To split string with delimeter.
Written On: 28-July-2015.
SELECT * FROM FN_SplitSTRING('1,2,3,5,6,4,8',',')
SELECT * FROM FN_SplitSTRING('NITISH, KUMAR, JHA, FROM, SAMASTIPUR',',')
SELECT * FROM FN_SplitSTRING('17*858*858*8569*89*58*nitish','*')
*/
AS
BEGIN
DECLARE @i INT= LEN(@Text)
WHILE @i <> 0
BEGIN
INSERT INTO @Table SELECT LTRIM(RTRIM(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text))))
SET @Text= RIGHT(@Text,LEN(@Text) - LEN(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text)+1)) )
IF(LEN(SUBSTRING(@Text,0,CHARINDEX(@Delimeter,@Text)+1)) = 0)
BEGIN
INSERT INTO @Table SELECT LTRIM(@Text)
SET @i= 1;
END
SET @i= @i-1
END
RETURN
END
GO
No comments:
Post a Comment