Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2014
    Posts
    5

    Unanswered: Split semicolon delimited string into n columns

    I have googled for a couple of ours now but found no DB2 compliant syntax for the following problem solution.

    I have a single column with a long, semicolon, separated string:

    Row1:Type;ProductionDate;Color;Costs;Reduction;Loc ation
    Row2:CarOne;12-12-2014;Blue;3155.12;-22;Island

    How can I separate those into columns? The number of columns in variable, could be 10 the next time I receive a .csv, the first row does always contain the column names.

    Can this be done without a function?

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    export this data
    try to reload data with import and format del into existing table(with columnnames as in row1 and null ) if columns in string-col are always the same layout
    see infocenter for detailed command export-import
    if data is in a file : write a script and read the colnames
    use this to create an import into table with the colnames
    eg read Type;ProductionDate;Color;Costs;Reduction;Loc ation and translate to
    insert into table (Type,ProductionDate,Color,Costs,Reduction,Locatio n) values (..
    Last edited by przytula_guy; 07-15-14 at 06:02.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jul 2014
    Posts
    5
    Hi przytula_guy,
    thanks for the reply.
    is there no other way? we use a custom java developed java
    import tool. I cant do any fancy scripting or ETL transformations.
    just have to take the data as it comes.

  4. #4
    Join Date
    Jul 2014
    Posts
    5

    Question

    Could I maybe transform all ; delimited strings into rows and then
    pivot those rows to columns? The first string is always the unique ID.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

  6. #6
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    you can write a Generic table function, if you at least at version 10.1.
    Cool thing is that you can define an actual number of columns which this UDF returns at runtime, but you have to construct your statement according to the input string.
    You will be able to do something like this:

    Code:
    Select T.Type, date(to_date(T.ProductionDate, 'DD-MM-YYYY')), ...
    From TABLE (myUDF('CarOne;12-12-2014;Blue;3155.12;-22;Island')) 
    AS T (
      Type varchar(10)
    , ProductionDate varchar(10)
    , Color varchar(10)
    , Costs varchar(10)
    , Reduction varchar(10)
    , Location varchar(10)
    )
    If you have a string with different set of fields, you have to specify a corresponding set of fields (their number must correspond to the number of tokens in the parameter) in the 'AS' clause and appropriate set of expressions in the SELECT list. But you use the same implementation of myUDF in all cases.
    It's quite easy to write such java UDF, but you must be familiar how to do this...
    Regards,
    Mark.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The final CTE (Common Table Expression) example that follows "SET @d4" in this post will parse the strings on any separator. The example is written using Microsoft SQL Server, but it ought to be portable to DB2 with very little effort. I don't have a DB2 instance available today, but let me know if you have trouble converting it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Another option without any external function:

    Code:
    select 
      x.type
    , date(to_date(x.ProductionDate, 'DD-MM-YYYY')) ProductionDate
    , x.Color, x.Costs, x.Reduction, x.Location
    from 
      table (values 
      'CarOne;12-12-2014;Blue;3155.12;-22;Island'
    , 'CarTwo;13-12-2014;Blue;3155.12;-22;Island'
      ) t(s)
    , xmltable('let $d := for $t in tokenize($s, ";") return <i>{$t}</i> return <d>{$d}</d>' passing t.s as "s"
    columns 
      Type           varchar(10) path 'i[1]'
    , ProductionDate varchar(10) path 'i[2]'
    , Color          varchar(10) path 'i[3]'
    , Costs          double      path 'i[4]'
    , Reduction      integer     path 'i[5]'
    , Location       varchar(10) path 'i[6]'
    ) x;
    
    TYPE       PRODUCTIONDATE COLOR      COSTS                    REDUCTION   LOCATION  
    ---------- -------------- ---------- ------------------------ ----------- ----------
    CarOne     12.12.2014     Blue         +3,15512000000000E+003         -22 Island    
    CarTwo     13.12.2014     Blue         +3,15512000000000E+003         -22 Island
    Regards,
    Mark.

  9. #9
    Join Date
    Jul 2014
    Posts
    5
    Hi mark.b

    Thanks for the feedback! Great idea!


    Pat Phelan, also many thanks to you for the comment!

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    This is a nice solution when row1 is static, but op said it would be dynamic as well. That being the case, you may want to take a closer look at the link that Pat was pointing you towards.
    Dave

  11. #11
    Join Date
    Jul 2014
    Posts
    5
    OK. Thanks Dave!

  12. #12
    Join Date
    Feb 2012
    Posts
    23
    Maybe you could have a look at https://www.ibm.com/developerworks/c...tagg12?lang=en.

    Maybe that can help you now or may be handy in the future.
    I've been using LISTAGG a lot.

    Another great example by Serge Rielau and Rick Swagerman about LISTAGG:
    https://www.ibm.com/developerworks/c...istagg?lang=en

Posting Permissions

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