Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Field Extration Problem

    I have a task that I am really stuck with and hope someone can help me out. I have a column in a view that can either contain null, one, or multiple values. See column B in examples below.

    A B
    -----------------------------
    M00001 null
    M00002 123, 456, 789
    M00003 456
    M00004 888
    M00005 154, 625
    .
    .
    .

    What I need to do is display a row for each item in column B if the column contains multiple values. See example below.

    A B
    -----------------------------
    M00001 null
    M00002 123
    M00002 456
    M00002 789
    M00003 456
    M00004 888
    M00005 154
    M00005 625
    .
    .
    .


    Thanks,

    DB

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    Code:
    try this one
    declare @str table(id varchar(12) , data varchar(128))
    insert into @str select 'M00001', null
    insert into @str select 'M00002', '123,456,789'
    insert into @str select 'M00003', '456'
    insert into @str select 'M00004', '888'
    insert into @str select 'M00005', '154,625'
    
    SELECT id,
    SUBSTRING(data, v.Number - 1,
    COALESCE(NULLIF(CHARINDEX(',', isnull(data,' '), v.Number), 0), LEN(data) + 1) - v.Number + 1) AS value
    FROM @str AS s
    INNER JOIN master..spt_values AS v ON v.Type = 'p'
    WHERE SUBSTRING(',_' + isnull(data,' '), v.Number, 1) = ','
    Last edited by bklr; 04-16-09 at 01:05.

  3. #3
    Join Date
    Nov 2005
    Posts
    91
    BKLR this is exactly what I was looking for, thank you very much!!!

    I am having problems however modifying the code that you provided to run against a table verus a table that is stored in a variable and don't quite understand the need for the master.spt_values field. Can you tell me what's wrong with my code below?

    SELECT dbo.TABLENAME.[UNIT #], SUBSTRING(dbo.TABLENAME.[TYPE2 CODE], v.number - 1,
    COALESCE (NULLIF (CHARINDEX(',', ISNULL(dbo.TABLENAME.[TYPE2 CODE], N' '), v.number), 0),
    LEN(dbo.TABLENAME.[TYPE2 CODE]) + 1) - v.number + 1) AS value
    FROM dbo.TABLENAME WITH (nolock) INNER JOIN
    master.dbo.spt_values AS v ON v.type = 'p'
    WHERE (SUBSTRING(',_' + ISNULL(dbo.TABLENAME.[TYPE2 CODE], ' '), v.number, 1) = ',')


    Thanks,

    DB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •