split string and return as table column

  • Hi All,

    I need a help in the following:

    My table has a column as comma separated sting.

    I want to split the values and return the result as column along with existing column.

    col1 col2 col3

    1 2 test,to,split

    2 5 test1,to1,split1

    and I want the result as,

    1,2,test,to,split

    2,5,test1,to1,split1

    Is it possible to do this?

    Thanks for your help.

    -Maria

  • yes this is possible,

    you will need to use the Substring funciton to split out the CSV values.

    haev a look at this article for an efficent way of dealing with this..

    http://www.sqlservercentral.com/articles/TSQL/62867/"> http://www.sqlservercentral.com/articles/TSQL/62867/

  • I use a function to turn comma separated lists in varchars into tables (don't ask - i know it breaks 1st nornal form - it's something i inherited)

    you should be able to modify this to pass in your primary key as well

    I haven't tried using this table value function as part of an inner join like this, but it's worth a try

    CREATE FUNCTION fn_GetTableByIDList (@list ntext,

    @delimiter nchar(1) = N',')

    RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, number int NOT NULL) AS

    BEGIN

    DECLARE @pos int,

    @textpos int,

    @chunklen smallint,

    @STR nvarchar(4000),

    @tmpstr nvarchar(4000),

    @leftover nvarchar(4000)

    SET @textpos = 1

    SET @leftover = ''

    WHILE @textpos <= datalength(@list) / 2

    BEGIN

    SET @chunklen = 4000 - datalength(@leftover) / 2

    SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))

    SET @textpos = @textpos + @chunklen

    SET @pos = charindex(@delimiter, @tmpstr)

    WHILE @pos > 0

    BEGIN

    SET @STR = substring(@tmpstr, 1, @pos - 1)

    INSERT @tbl (number) VALUES(convert(int, @STR))

    SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))

    SET @pos = charindex(@delimiter, @tmpstr)

    END

    SET @leftover = @tmpstr

    END

    IF ltrim(rtrim(@leftover)) <> ''

    INSERT @tbl (number) VALUES(convert(int, @leftover))

    RETURN

    END

    MVDBA

  • Hi Steve,

    Thanks for your reply.

    I could get the comma separated values as rows. How to pivot that into columns?

    I am very new to this sqlserver.

    Thanks for your help.

    -Maria

  • Hi Micheal,

    When I try to call the function like this,

    select * from dbo.fn_GetTableByIDList('test,to,split',',')

    I am getting the following error:

    Conversion failed when converting the nvarchar value 'test' to data type int.

    Thanks for your help.

    -Maria

  • are there same number of commas in col3 for every row and how is your output going to be used?

  • Hi,

    Thanks for helping me.

    Yes, there are same number of commas for the given query.

    Thanks,

    Maria

  • How are you going to use the result set?

  • Hi,

    I want to display that data in a gridview and pass that table

    for further calculations .

    -Maria

  • Personally, I would return all columns as one comma separated string

    (select col1+','+ col2 + ',' + col3 from yourtable)

    and parse it in the client.

    Returning a dataset from the SQL server will most likely require you to create a stored proc and loop thru a cursor.

  • I know this is SS forum but here is one way of doing it on the client.

    if (!IsPostBack)

    {

    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["testDBconnString"].ToString());

    SqlCommand comm = new SqlCommand("select col1+','+col2+','+col3 as line from yourtable", conn);

    conn.Open();

    SqlDataReader rdr = comm.ExecuteReader();

    //assuming there are 2 + 3 = 5 cols now

    DataTable dt = new DataTable();

    if (rdr.HasRows)

    {

    dt.Columns.Add("col1", typeof(string));

    dt.Columns.Add("col2", typeof(string));

    dt.Columns.Add("col3", typeof(string));

    dt.Columns.Add("col4", typeof(string));

    dt.Columns.Add("col5", typeof(string));

    while (rdr.Read())

    {

    string[] cols = rdr.GetString(0).Split(new char[] { ',' });

    DataRow dr = dt.NewRow();

    for (int i=0;i<cols.Length;i++)

    {

    dr = cols;

    }

    dt.Rows.Add(dr);

    }

    }

    rdr.Close();

    conn.Close();

    GridView1.DataSource = dt;

    GridView1.DataBind();

  • michael vessey (12/17/2008)


    I use a function to turn comma separated lists in varchars into tables (don't ask - i know it breaks 1st nornal form - it's something i inherited)

    Nicely done. Looks very similar to Sergiy's and Adam Machanic's work. Consider this, though... in SQL Server 2005, there's no longer a need to use the TEXT data type. In fact, it has been officially deprecated. That also means that there's no longer a need for a WHILE loop in the code to handle the deficiencies of the TEXT data type.

    With that in mind, consider the following as a suggestion to an alternate...

    [font="Arial Black"]

    Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays

    [/font][/url]

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • sorry - the original function was designed to split integers - you should be able to modify the code so output a list of varchars rather than int

    MV

    MVDBA

  • Hi Jeff,

    I tried your tally table and splitting the string as rows in a table output.

    But how to make them as columns? or use crosstab to make it as a column?

    For eg.

    "test,to split" returns as 3 rows

    test

    to

    split

    But I want it as

    test to split - as a 3 column data along with other entries

    Thanks,

    Maria

  • Maria,

    If your table has a lot of records , you should write a stored proc and use a cursor. if not here is your function.

    this is how you would use it

    SELECT

    col1

    ,col2

    ,[dbo].[colsplit](col3,0)

    ,[dbo].[colsplit](col3,1)

    ,[dbo].[colsplit](col3,2)

    from yourtable

    CREATE FUNCTION colsplit

    (

    @colval nvarchar(50),

    @which int

    )

    RETURNS nvarchar(50)

    AS

    BEGIN

    DECLARE @pos int,

    @len int

    set @pos = 0

    while (@which > 0)

    begin

    set @pos = charindex(',',@colval,@pos+1)

    set @which = @which - 1

    end

    set @len = charindex(',',@colval,@pos+1)

    set @len = @len - @pos - 1

    if @len > 0

    RETURN substring(@colval,@pos+1,@len)

    RETURN @colval

    END

    GO

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply