SQL Server Hosting – HostForLIFE.eu :: How to Convert String To Table List in MS-SQL?

Peter Cole | May 17, 2017 | SQL 2016 Hosting | No Comments

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

CREATE FUNCTION[dbo].[UF_StrToTable](@String VARCHAR(MAX), @Delimiter CHAR(1))

RETURNS @Temptable TABLE(Result VARCHAR(8000))

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 and here is the output:

Related Posts

About The Author

Pete