Click here to Skip to main content
Click here to Skip to main content

Tagged as

Convert a CSV delimited string to table column in SQL SERVER

, 25 Jul 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
How to convert a delimited string in SQL Server to a table column.

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',',')
//----------------------------------------------  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Kumar Pankaj Verma
Technical Lead E. Soft Technologies
India India
Never try out to sort other's problem. Tell them the way only ........... Let do themselves.
Follow on   Google+

Comments and Discussions

 
GeneralDoesn't work when we have double quotes PinmemberKhan_alpha11-Nov-14 11:57 
QuestionPass empty parameters PinprofessionalSriram Ramachandran11-Aug-14 22:59 
GeneralMy vote of 3 Pinmemberjdschilling26-Jul-13 8:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 25 Jul 2013
Article Copyright 2013 by Kumar Pankaj Verma
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid