Splitting of comma separated values

Question

I have some columns with comma-separated values. For example

A

as,ad,af,ag

My manager wants the output looks like this:

A

All these should come under one row. I guess we need to use something line break.

Hi can we use replace with char(13)+char(10) something. This one also works...

Thanks, Shashra

duplucate of http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco — Gangnus, Dec 11 '12 at 10:03

score 7 · Accepted Answer · answered Mar 30 '11 at 14:55

You can create a user defined UDF like the one shown below. Then, just pass in the comma separated list from another query and it will return a table with each value in a separate row.

CREATE FUNCTION [dbo].[fnSplitStringAsTable] 
(
    @inputString varchar(MAX),
    @delimiter char(1) = ','
)
RETURNS 
@Result TABLE 
(
    Value varchar(MAX)
)
AS
BEGIN
    DECLARE @chIndex int
    DECLARE @item varchar(100)

    -- While there are more delimiters...
    WHILE CHARINDEX(@delimiter, @inputString, 0) <> 0
        BEGIN
            -- Get the index of the first delimiter.
            SET @chIndex = CHARINDEX(@delimiter, @inputString, 0)

            -- Get all of the characters prior to the delimiter and insert the string into the table.
            SELECT @item = SUBSTRING(@inputString, 1, @chIndex - 1)

            IF LEN(@item) > 0
                BEGIN
                    INSERT INTO @Result(Value)
                    VALUES (@item)
                END

            -- Get the remainder of the string.
            SELECT @inputString = SUBSTRING(@inputString, @chIndex + 1, LEN(@inputString))
        END

    -- If there are still characters remaining in the string, insert them into the table.
    IF LEN(@inputString) > 0
        BEGIN
            INSERT INTO @Result(Value)
            VALUES (@inputString)
        END

    RETURN 
END

score 6 · Answer 2 · answered Apr 20 '13 at 08:43

6

Check this without using User Defined Function

DECLARE @param VARCHAR(MAX)
SET @param = 'as,ad,af,ag'

SELECT Split.a.value('.', 'VARCHAR(100)') AS ColName  
FROM  
(
     SELECT CONVERT(XML, '<M>' + REPLACE(ColName, ',', '</M><M>') + '</M>') AS ColName  
     FROM  (SELECT @param AS ColName) TableName
 ) AS A CROSS APPLY ColName.nodes ('/M') AS Split(a)

answered Apr 20 '13 at 08:43

bvr

4,786
1
20
24

This is the solution I needed because I do not have permission to modify the database schema. – Jin Kim Oct 15 '15 at 14:12

score -2 · Answer 3 · answered Mar 30 '11 at 14:55

-2

You want to use a string splitting function, like the one found here for example.

Once you've created this function, you can:

select string as A
    from YourTable
        cross apply dbo.fnParseStringTSQL(A,',')

answered Mar 30 '11 at 14:55

Joe Stefanelli

132,803
19
237
235

I TRIED LIKE THIS BUT I ITS NOT WORKING. IT SHOWING ALL THOSE 3 COMMA SEPERATED VALUES 3 TIMES(MEANS SHOWING 3 DUPLICATED ROWS WITH SAME VALUES)... – Shahsra Mar 30 '11 at 15:24
@Shahsra: Can you provide more details on the data you're testing with? – Joe Stefanelli Mar 30 '11 at 15:48

Splitting of comma separated values

A

3 Answers3

Linked