how to select substring after

mlybarger · #1 (**permalink**) 12-08-10, 16:13

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!

Lenny77 · #2 (**permalink**) 12-08-10, 18:22

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

yvanroy · #3 (**permalink**) 12-10-10, 16:09

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech