Convert a Delimited String to rows based on a delimiter

——————————————————————————————————————-

— 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

— Test Query : SELECT * FROM [dbo].[DelimitedStringToRows](‘1 2 3′,’ ‘)

---

— Modified By :
— Modified On :

— Modification :

---

ALTER 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