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.
![]()