SQL delimited string to table function

Sometimes data suppliers do not make their data easy to separate. Other times you might want to split some other data up.

This SQL Table function will take a string comprising a list of substrings with a specified specified delimiter and returns a table containing the substrings

-- =============================================
-- Author: Technology Is Not Dull
-- Create date: 19/01/2016
-- Description: Takes a delimited string list and returns a table
-- =============================================
CREATE FUNCTION [dbo].[stringToTable]
(
@string VARCHAR(200),
@delimiter char(1)
)
RETURNS
@rettable TABLE
(
stringbit varchar(50)
)
AS
BEGIN
--***# select * from dbo.stringToTable(POLYETHYLENE GLYCOL 3350; POTASSIUM CHLORIDE; SODIUM BICARBONATE; SODIUM CHLORIDE; SODIUM SULFATE ANHYDROUS',';')
DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0
            SET @end = LEN(@string) + 1
     
        INSERT INTO @rettable (stringbit)
                  VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
     
    END
    RETURN
END
GO