MS-SQL Convert String To Table List

This blog helps to convert string to table list, using SQL user defined function.

Create a function

When we start programming in any language, mostly we use loop and we hit Server side code to list the data every time. This function is helpful in reducing our Server side code & increase Application performance. 

CREATE FUNCTION[dbo].[UF_StrToTable](@String VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @Temptable TABLE(Result VARCHAR(8000))
AS
BEGIN
DECLARE @INDEX int, @SLICE VARCHAR(8000)
SELECT @INDEX = 1
IF LEN(@String) < 1 OR @String IS NULL
return
WHILE @INDEX != 0
BEGIN
SET @INDEX = CHARINDEX(@Delimiter, @String)
IF @INDEX != 0
BEGIN
SET @SLICE = LEFT(@String, @INDEX - 1)
END
ELSE
BEGIN
SET @SLICE = @String
END
IF(LEN(@SLICE) > 0)
BEGIN
INSERT INTO @Temptable(Result) VALUES(@SLICE)
END
SET @String = RIGHT(@String, LEN(@String) - @INDEX)
IF LEN(@String) = 0
break
END
RETURN
END

Output

We can send the list as a string, as it converts against as a list of rows.

SELECT Result FROM DBO.UF_StrToTable('1,2,3,4,5',',')

Run the query given above in SQL Server & see the result.