none
How to parse out data RRS feed

  • Question

  • This is the type of data I have within my table and I need to take it from this to the example below

     

    Types                               First Name         Last Name

    ---------                               ----------------         ----------------

    6L4XX,6L5XX,6L8XX,6L9XX            Bob                   Smith
    6L4XX,6L5XX,6L8XX,6L9XX            Dave                  Johnson

     

     

     

     

    Types            First Name        Last Name

    ---------            ----------------        ---------------

    6L4XX            Bob                   Smith

    6L5XX            Bob                   Smith

    6L8XX            Bob                   Smith

    6L9XX            Bob                   Smith

    6L4XX            Dave                  Johnson

    6L5XX            Dave                  Johnson

    6L8XX            Dave                  Johnson

    6L9XX            Dave                  Johnson

     

     

    I have to do this for MANY rows but I don't want to use a cursor.  Should I be using Dynamic SQL?  If so how should I go about starting out.

     

    Thank You

     


                     

    Friday, April 25, 2008 1:29 PM

Answers

  • This uses a loop, and Jens Suessmeyer's Split Function to split the strings, but if you have multiple variations of string, this is the only way I can come up with to do it:

     

    Code Snippet

    -- Disable results counting

    SET NOCOUNT ON

     

    -- Prepare Testing Environment

    DECLARE @TABLE TABLE(Types varchar(300), FirstName varchar(50), LastName varchar(50))

    INSERT INTO @TABLE SELECT '6L4XX,6L5XX,6L8XX,6L9XX', 'Bob', 'Smith'

    INSERT INTO @TABLE SELECT '6L4XX,6L5XX,6L8XX,6L9XX', 'Dave', 'Johnson'

     

    -- Start Actual Solution Code

    DECLARE @ReturnResults TABLE (Type varchar(30), FirstName varchar(50), LastName varchar(50))

    DECLARE @Types varchar(300)

     

    SELECT @Types = min(Types)

    FROM @TABLE

     

    WHILE @Types IS NOT NULL

    BEGIN

    INSERT INTO @ReturnResults (Type, FirstName, LastName)

    SELECT sub.Value, t.FirstName, t.LastName

    FROM @Table t, (SELECT * FROM dbo.split(',', @Types)) sub

    WHERE t.Types = @Types

     

    SELECT @Types = min(Types)

    FROM @TABLE

    WHERE Types > @Types

     

    END

     

    SELECT * FROM @ReturnResults

     

     

    Jens Suessmeyer's Split function is part of this example by Arnie Rowland.

    Friday, April 25, 2008 1:47 PM
    Moderator
  • Actually, you can do it without looping with a CROSS APPLY.  It just dawned on me:

     

     

    Code Snippet

    -- Disable results counting

    SET NOCOUNT ON

     

    -- Prepare Testing Environment

    DECLARE @TABLE TABLE(Types varchar(300), FirstName varchar(50), LastName varchar(50))

    INSERT INTO @TABLE SELECT '6L4XX,6L5XX,6L8XX,6L9XX', 'Bob', 'Smith'

    INSERT INTO @TABLE SELECT '6L4XX,6L5XX,6L8XX,6L9XX', 'Dave', 'Johnson'

     

    -- Start Actual Solution Code

    SELECT sub.Value, t.FirstName, t.LastName

    FROM @Table t

    CROSS APPLY(SELECT * FROM dbo.split(',', Types)) sub

     

     

    Friday, April 25, 2008 1:48 PM
    Moderator
  • Jerry,

     

    The split() function with Cross apply will handle any array size up to 8000 characters

     

    Code Snippet

    DECLARE @TABLE TABLE(Types varchar(300), FirstName varchar(50), LastName varchar(50))

     

    INSERT INTO @TABLE SELECT '6L1XX,6L2XX,6L3XX,6L4XX,6L5XX,6L6XX,6L7XX,6L8XX,6L9XX', 'Bob', 'Smith'

    INSERT INTO @TABLE SELECT '6L4XX,6L5XX,6L8XX,6L9XX', 'Dave', 'Johnson'

     

     

    SELECT sub.Value, t.FirstName, t.LastName

    FROM @Table t

    CROSS APPLY(SELECT * FROM dbo.split(',', Types)) sub

     

    -- Results

     

    Value FirstName LastName

    ---------- ---------- ----------

    6L1XX Bob Smith

    6L2XX Bob Smith

    6L3XX Bob Smith

    6L4XX Bob Smith

    6L5XX Bob Smith

    6L6XX Bob Smith

    6L7XX Bob Smith

    6L8XX Bob Smith

    6L9XX Bob Smith

    6L4XX Dave Johnson

    6L5XX Dave Johnson

    6L8XX Dave Johnson

    6L9XX Dave Johnson

     

     

    You just have to add the split() function to your database since it is a dependant object for this solution to work.  Let us know how it goes.

    Friday, April 25, 2008 2:27 PM
    Moderator
  • Code Snippet

    -- Here I have used a variant of Jonathon's test table:
    DECLARE @TABLE TABLE(Types varchar(160), FirstName varchar(10), LastName varchar(10))
    INSERT INTO @TABLE SELECT '6L4XX   6L5XX   6L8XX   6L9XX', 'Bob', 'Smith'
    INSERT INTO @TABLE SELECT '6L4XX   6L5XX   6L8XX   6L9XX', 'Dave', 'Johnson'
    --select * from @table

     

    select
      substring(types, 8*n-7, 8) as Types,
      firstName,
      lastName
    from @table
    join
    ( select 1 as n union all select 2 union all select 3 union all select 4 union all
      select 5 union all select 6 union all select 7 union all select 8 union all
      select 9 union all select 10 union all select 11 union all select 12 union all
      select 13 union all select 14 union all select 15 union all select 16 union all
      select 17 union all select 18 union all select 19 union all select 20
    ) as numbers
      on n <= (len(types)+7)/8

     

    /* -------- Sample Output --------
    Types    firstName  lastName
    -------- ---------- ----------
    6L4XX    Bob        Smith
    6L5XX    Bob        Smith
    6L8XX    Bob        Smith
    6L9XX    Bob        Smith
    6L4XX    Dave       Johnson
    6L5XX    Dave       Johnson
    6L8XX    Dave       Johnson
    6L9XX    Dave       Johnson
    */

     

     

     

    Friday, April 25, 2008 2:47 PM
    Moderator

All replies

  • If there are ALWAYS 5 characters that you wish to parse option 1, if it varies and you are parsing to the comma option2

    Code Snippet

    DECLARE @var VARCHAR(40)

    SET @var = '6L4XX,6L5XX,6L8XX,6L9XX '

    SELECT LEFT(@var, 5)

    SELECT SUBSTRING(@var, 1, CHARINDEX(',',@var))

     

     

     

     

    Friday, April 25, 2008 1:38 PM
    Moderator
  • Thank you for your reply.

     

    I have to use the second option because I will never know the exact length of the each value in front of the comma. 

     

    But I still need to get it to loop that code only returns the value in front of the first comma.

     

     

    If I miss something your a implying i'm sorry.

     

    Friday, April 25, 2008 1:44 PM
  • This uses a loop, and Jens Suessmeyer's Split Function to split the strings, but if you have multiple variations of string, this is the only way I can come up with to do it:

     

    Code Snippet

    -- Disable results counting

    SET NOCOUNT ON

     

    -- Prepare Testing Environment

    DECLARE @TABLE TABLE(Types varchar(300), FirstName varchar(50), LastName varchar(50))

    INSERT INTO @TABLE SELECT '6L4XX,6L5XX,6L8XX,6L9XX', 'Bob', 'Smith'

    INSERT INTO @TABLE SELECT '6L4XX,6L5XX,6L8XX,6L9XX', 'Dave', 'Johnson'

     

    -- Start Actual Solution Code

    DECLARE @ReturnResults TABLE (Type varchar(30), FirstName varchar(50), LastName varchar(50))

    DECLARE @Types varchar(300)

     

    SELECT @Types = min(Types)

    FROM @TABLE

     

    WHILE @Types IS NOT NULL

    BEGIN

    INSERT INTO @ReturnResults (Type, FirstName, LastName)

    SELECT sub.Value, t.FirstName, t.LastName

    FROM @Table t, (SELECT * FROM dbo.split(',', @Types)) sub

    WHERE t.Types = @Types

     

    SELECT @Types = min(Types)

    FROM @TABLE

    WHERE Types > @Types

     

    END

     

    SELECT * FROM @ReturnResults

     

     

    Jens Suessmeyer's Split function is part of this example by Arnie Rowland.

    Friday, April 25, 2008 1:47 PM
    Moderator
  • Actually, you can do it without looping with a CROSS APPLY.  It just dawned on me:

     

     

    Code Snippet

    -- Disable results counting

    SET NOCOUNT ON

     

    -- Prepare Testing Environment

    DECLARE @TABLE TABLE(Types varchar(300), FirstName varchar(50), LastName varchar(50))

    INSERT INTO @TABLE SELECT '6L4XX,6L5XX,6L8XX,6L9XX', 'Bob', 'Smith'

    INSERT INTO @TABLE SELECT '6L4XX,6L5XX,6L8XX,6L9XX', 'Dave', 'Johnson'

     

    -- Start Actual Solution Code

    SELECT sub.Value, t.FirstName, t.LastName

    FROM @Table t

    CROSS APPLY(SELECT * FROM dbo.split(',', Types)) sub

     

     

    Friday, April 25, 2008 1:48 PM
    Moderator
  • Thank you for correcting to CROSS APPLY, Jonathan.  I was going to suggest that.    Also, from the original post, is the number of TYPES limited to just four?

     

    Friday, April 25, 2008 1:53 PM
    Moderator
  • Are there other recommend ways for passing arrays through stored procedure parameters, other than passing a long comma delimited varchar?
    Friday, April 25, 2008 1:57 PM
  • SQL 2008 allows you to pass a table variable as a parameter which will help solve this problem.

     

    Friday, April 25, 2008 2:00 PM
    Moderator
  • This method and passing the data as XML are the two current primary methods used in SQL Server 2005.  In your case your data might be consistent enough in size that you consider what Jonathan said in his first comment and that you pass your data in using fixed blocks rather than using a delimited string.  Processing blocked data is frequently faster than processing delimited data.  You can coerce your data into fixed length blocks by padding out each piece with spacing to fill out each block.

     

    Friday, April 25, 2008 2:13 PM
    Moderator
  • Kent,

    No it can be 1 type or 20 types.  This is part of a data dump that someone else has done that I have to clean up for sql.

    I'm going to try the different suggestions

     

    Thank you everyone for your quick response. 

     

     

    JErry

     

    Friday, April 25, 2008 2:22 PM
  • Jerry,

     

    The split() function with Cross apply will handle any array size up to 8000 characters

     

    Code Snippet

    DECLARE @TABLE TABLE(Types varchar(300), FirstName varchar(50), LastName varchar(50))

     

    INSERT INTO @TABLE SELECT '6L1XX,6L2XX,6L3XX,6L4XX,6L5XX,6L6XX,6L7XX,6L8XX,6L9XX', 'Bob', 'Smith'

    INSERT INTO @TABLE SELECT '6L4XX,6L5XX,6L8XX,6L9XX', 'Dave', 'Johnson'

     

     

    SELECT sub.Value, t.FirstName, t.LastName

    FROM @Table t

    CROSS APPLY(SELECT * FROM dbo.split(',', Types)) sub

     

    -- Results

     

    Value FirstName LastName

    ---------- ---------- ----------

    6L1XX Bob Smith

    6L2XX Bob Smith

    6L3XX Bob Smith

    6L4XX Bob Smith

    6L5XX Bob Smith

    6L6XX Bob Smith

    6L7XX Bob Smith

    6L8XX Bob Smith

    6L9XX Bob Smith

    6L4XX Dave Johnson

    6L5XX Dave Johnson

    6L8XX Dave Johnson

    6L9XX Dave Johnson

     

     

    You just have to add the split() function to your database since it is a dependant object for this solution to work.  Let us know how it goes.

    Friday, April 25, 2008 2:27 PM
    Moderator
  • I added the Split Function

     

    and used the code and it works perfect.

     

    Thank you all so very much 

     

    Have a great weekend

     

    Jerry

     

    Friday, April 25, 2008 2:30 PM
  • Code Snippet

    -- Here I have used a variant of Jonathon's test table:
    DECLARE @TABLE TABLE(Types varchar(160), FirstName varchar(10), LastName varchar(10))
    INSERT INTO @TABLE SELECT '6L4XX   6L5XX   6L8XX   6L9XX', 'Bob', 'Smith'
    INSERT INTO @TABLE SELECT '6L4XX   6L5XX   6L8XX   6L9XX', 'Dave', 'Johnson'
    --select * from @table

     

    select
      substring(types, 8*n-7, 8) as Types,
      firstName,
      lastName
    from @table
    join
    ( select 1 as n union all select 2 union all select 3 union all select 4 union all
      select 5 union all select 6 union all select 7 union all select 8 union all
      select 9 union all select 10 union all select 11 union all select 12 union all
      select 13 union all select 14 union all select 15 union all select 16 union all
      select 17 union all select 18 union all select 19 union all select 20
    ) as numbers
      on n <= (len(types)+7)/8

     

    /* -------- Sample Output --------
    Types    firstName  lastName
    -------- ---------- ----------
    6L4XX    Bob        Smith
    6L5XX    Bob        Smith
    6L8XX    Bob        Smith
    6L9XX    Bob        Smith
    6L4XX    Dave       Johnson
    6L5XX    Dave       Johnson
    6L8XX    Dave       Johnson
    6L9XX    Dave       Johnson
    */

     

     

     

    Friday, April 25, 2008 2:47 PM
    Moderator