Sql Server– A function to create a temp-table from a delimited string

Here is a SqlServer user defined function that takes as input a delimiter separated string and the delimiter and returns a table.

CREATE FUNCTION [dbo].[StringToTable]
(   
    @inString varchar(8000),
    @delimiter varchar(30)
)
returns @tempTable table (keyvalue varchar(50))
AS
begin
    declare @item varchar(50)
   
    -- remove leading comma
    if substring(@inString,1,1) = @delimiter
        begin
            set @inString = substring(@inString,2,len(@inString))
        end

    -- put in the comma if not there
    if substring(@inString, len(@inString),1) != @delimiter
        begin
            set @inString = @inString + @delimiter
        end

    -- go through and put all the strings in the table
    while charindex(@delimiter,@inString) > 0
        begin
            set @item = substring(@inString,1,charindex@delimiter,@inString)-1)
            insert @tempTable (keyvalue) values (@item)
            set @inString = substring(@inString,charindex@delimiter,@inString)+1, len(@inString))
        end

return

end

Using the StringToTable method:

select * from StringToTable ('Hello,World', ',')

returns:

keyvalue
Hello
World