-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-- Created By : Haroon Mushtaq Abbasi
-- Created On : 23-10-2019
-- Description : Convert a Delimited String to rows based on a delimiter like ',' , ' ' , '-' , etc
-- Input : DelimitedString, Delimeter
-- Output : Dataset containing the separated strings in multiple rows of a column
-- Test Query : SELECT * FROM [dbo].[DelimitedStringToRows]('abc,def,ghi',',')
-- Test Query : SELECT * FROM [dbo].[DelimitedStringToRows]('1 2 3',' ')
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-- Modified By :
-- Modified On :
-- Modification :
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[DelimitedStringToRows]
(
@DelimitedString AS VARCHAR(4000),
@Delimiter AS VARCHAR(1)
)
RETURNS
@Result TABLE(Value VARCHAR(50))
AS
BEGIN
DECLARE @str VARCHAR(20)
DECLARE @ind INT
IF(@DelimitedString IS NOT NULL)
BEGIN
SET @DelimitedString = LTRIM(RTRIM(@DelimitedString))
SET @ind = CHARINDEX(@Delimiter,@DelimitedString)
WHILE @ind > 0
BEGIN
SET @str = RTRIM(LTRIM(SUBSTRING(@DelimitedString,1,@ind-1)))
SET @DelimitedString = SUBSTRING(@DelimitedString,@ind+1,LEN(@DelimitedString)-@ind)
INSERT INTO @Result VALUES (@str)
SET @ind = CHARINDEX(@Delimiter,@DelimitedString)
END
SET @str = @DelimitedString
INSERT INTO @Result VALUES (@str)
END
RETURN
END