Convert a CSV delimited string to table column in SQL SERVER

Introduction  

How to convert a delimited string in SQL Server to a table column. 

Background 

Some times in SQL server we need to convert a delimited string it to table column. This becomes very important if we receive a delimited string in a stored procedure as argument and we want to use these value to use in "IN CLAUSE" in a T-SQL statement.

Using the code 

Below given a table valued function which converts CSV values and return data as table.

1. Create this function in SQL server 

//---------------------------------------
/****** Object:  UserDefinedFunction [dbo].[CSVtoTable]    Script Date: 07/25/2013 09:12:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Kumar Pankaj Verma>
-- Create date: <05-Apr-2011>
-- Description:    <Convert CSV to Table>
-- =============================================
CREATE FUNCTION [dbo].[CSVtoTable]
(
    @LIST varchar(7000),
    @Delimeter varchar(10)
)
RETURNS @RET1 TABLE (RESULT BIGINT)
AS
BEGIN
    DECLARE @RET TABLE(RESULT BIGINT)
    
    IF LTRIM(RTRIM(@LIST))='' RETURN  

    DECLARE @START BIGINT
    DECLARE @LASTSTART BIGINT
    SET @LASTSTART=0
    SET @START=CHARINDEX(@Delimeter,@LIST,0)

    IF @START=0
    INSERT INTO @RET VALUES(SUBSTRING(@LIST,0,LEN(@LIST)+1))

    WHILE(@START >0)
    BEGIN
        INSERT INTO @RET VALUES(SUBSTRING(@LIST,@LASTSTART,@START-@LASTSTART))
        SET @LASTSTART=@START+1
        SET @START=CHARINDEX(@Delimeter,@LIST,@START+1)
        IF(@START=0)
        INSERT INTO @RET VALUES(SUBSTRING(@LIST,@LASTSTART,LEN(@LIST)+1))
    END
    
    INSERT INTO @RET1 SELECT * FROM @RET
    RETURN 
END
GO
//---------------------------------------------- 

2. Use of function in T_SQL Statement. 

//---------------------------------------------- 
SELECT * FROM [dbo].[CSVtoTable]('100,200,300,400,500',',')
//----------------------------------------------