MS-SQL Convert String To Table List

In this blog, I will demonstrate how to convert string into table list the dynamically using MS-SQL user define function.

Create a function:

When we start programming in any language, mostly we use loop & every time to hit server side code for list data. This function helpful to reduce 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, it converts against as a list of rows.

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

Run the above Query in sql server & see the result