Regexp magic ! (I hope) [message #662041] |
Fri, 14 April 2017 18:20 |
Amine
Messages: 376 Registered: March 2010
|
Senior Member |
|
|
hi all,
it's been a while ! I missed you !
I need to check that a varchar satisfies one of the patterns below :
n1/A
n1+...+nx/A
n1+...+nx/A+n3+...+ny/B
I think regexp could do it, but how ?
Thanks in advance
Amine
|
|
|
|
|
Re: Regexp magic ! (I hope) [message #662093 is a reply to message #662089] |
Mon, 17 April 2017 22:38 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> select * from test_data
2 /
TEST_COL
---------------------------------------------------
454+787+965+6889+555+5555/B1+86+69+778+51+11+256/B2
454+787+965+6889+555+5555/B1
2 rows selected.
SCOTT@orcl_12.1.0.2.0> column obj format a15
SCOTT@orcl_12.1.0.2.0> column id format a15
SCOTT@orcl_12.1.0.2.0> select obj,
2 regexp_substr(ids,'[^+]+',1,column_value) id
3 from (select substr(obj_and_ids,instr(obj_and_ids,'/')+1) obj,
4 substr(obj_and_ids,1,instr(obj_and_ids,'/')-1) ids
5 from (select ltrim(regexp_substr(test_col,'[^/]+[^+]+',1,column_value),'+') obj_and_ids
6 from test_data,
7 table
8 (cast
9 (multiset
10 (select rownum
11 from dual
12 connect by level <= regexp_count(test_col,'/'))
13 as sys.odcinumberlist)))),
14 table
15 (cast
16 (multiset
17 (select rownum
18 from dual
19 connect by level <= regexp_count(ids,'\+')+1)
20 as sys.odcinumberlist))
21 /
OBJ ID
--------------- ---------------
B1 454
B1 787
B1 965
B1 6889
B1 555
B1 5555
B2 86
B2 69
B2 778
B2 51
B2 11
B2 256
B1 454
B1 787
B1 965
B1 6889
B1 555
B1 5555
18 rows selected.
|
|
|
|
|
|