Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010
    Posts
    8

    Unanswered: how to select substring before

    i have data such as:

    A
    ----

    A Data, More Data
    B Data, More Data
    C Data, More, Data
    D Data & More, Data
    E Data
    F More
    G Data, Data

    I'm trying to get the data selected as "everything before the first coma, or the first ampersand so the results would be:

    A
    -----------
    A Data
    B Data
    C Data
    D Data
    E Data
    F More
    G Data

    I try to use substr(A,1,posstr(',',A)-1), but that doesn't seem to work so nicely where there's no coma.

    Any help would be most appreciated.

    thanks!
    Last edited by mlybarger; 12-08-10 at 18:58. Reason: error

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb

    Try to use something like this:

    Code:
    select t1.A, left(t1.A, t2.posA)
    from 
    (select 'A abcd, efg%%%%%' A
       from sysibm.sysdummy1) t1
    join table
    (select t1.A, 
    case when locate(',', t1.A) < 1 then length(t1.A) 
         else locate(',', t1.A) - 1  end posA
       from sysibm.sysdummy1) t2
    on t1.A = t2.A
    Compare result to:

    Code:
    select t1.A, left(t1.A, t2.posA)
    from 
    (select 'A abcdefg%%%%%' A
       from sysibm.sysdummy1) t1
    join table
    (select t1.A, 
    case when locate(',', t1.A) < 1 then length(t1.A) 
         else locate(',', t1.A) - 1  end posA
       from sysibm.sysdummy1) t2
    on t1.A = t2.A
    Lenny

  3. #3
    Join Date
    Jun 2002
    Posts
    15

    how to select substring before

    or maybe something like this:

    Code:
    with
     sample as
      (select 'A abcd& efg%%%%%' as A
       from sysibm.sysdummy1
      )
    select
    left(sample.a,min(coalesce(nullif(locate(',', sample.A)-1,-1),length(sample.a))
                     ,coalesce(nullif(locate('&', sample.A)-1,-1),length(sample.a))
                     ,length(sample.a)
                     )
        )
    from sample
    ;

Posting Permissions

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