Click here to Skip to main content
13,094,732 members (99,086 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

24K views
6 bookmarked
Posted 24 Jul 2013

Convert a CSV delimited string to table column in SQL SERVER

, 25 Jul 2013
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.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionAnother way for to achieve same Pin
Member 131928457-Jul-17 5:05
memberMember 131928457-Jul-17 5:05 
GeneralDoesn't work when we have double quotes Pin
Khan_alpha11-Nov-14 10:57
memberKhan_alpha11-Nov-14 10:57 
QuestionPass empty parameters Pin
Sriram Ramachandran11-Aug-14 21:59
professionalSriram Ramachandran11-Aug-14 21:59 
GeneralMy vote of 3 Pin
jdschilling26-Jul-13 7:27
memberjdschilling26-Jul-13 7:27 

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

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

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