PL/SQL Regex search based on both characters and digits

Question

I have a table TA which has a column inv_ref with has data as below.

inv_ref
----------
MX/3280/20
CT/3281/20
CT/3109/20
MX/3272/20
RF/3275/20

My requirement is to fetch whereas middle 4 digit number of inv_ref between 3270 to 3299 also begin with either MX and CT.

select * from TA where regexp_like(inv_ref, 'CT/32[7-9][0-9]/20')

Above query only returns CT, how can return both CT and MX related values omitting RF?

Hi, it is your table very big ?? do you only need CT and MX ? or you have more combinations and you only want to omit one ? . I mean, if the table is not big, you could use an OR condition with two REGEXP_LIKE expressions — Roberto Hernandez, Jul 10 '20 at 08:40
Hi, table itself is big, and I need only CT and MX values for the moment, values in this column can only begin with CT, MX and RF. — Dilhan Nakandala, Jul 10 '20 at 08:49

score 4 · Answer 1 · answered Jul 10 '20 at 08:57

You can use:

SELECT *
FROM   TA
WHERE  REGEXP_LIKE(inv_ref, '^(CT|MX)/32[7-9][0-9]/20$')

Then if you have the test data:

CREATE TABLE TA ( inv_ref, is_valid ) AS
SELECT 'MX/3280/20', 'Valid' FROM DUAL UNION ALL
SELECT 'CT/3281/20', 'Valid' FROM DUAL UNION ALL
SELECT 'CT/3109/20', 'Invalid, number too low' FROM DUAL UNION ALL
SELECT 'MX/3272/20', 'Valid' FROM DUAL UNION ALL
SELECT 'RF/3275/20', 'Invalid, wrong start' FROM DUAL UNION ALL
SELECT 'CX/3299/20', 'Invalid, wrong start' FROM DUAL UNION ALL
SELECT 'MT/3270/20', 'Invalid, wrong start' FROM DUAL UNION ALL
SELECT 'ACT/3270/20', 'Invalid, wrong start' FROM DUAL;

This outputs:

INV_REF    | IS_VALID
:--------- | :-------
MX/3280/20 | Valid   
CT/3281/20 | Valid   
MX/3272/20 | Valid

db<>fiddle here

Sayan Malakshinov · Answer 2 · 2020-07-10T09:27:08.333

It would be much easier to add virtual generated columns:

alter table your_table add (
  P1 generated always as (regexp_substr(inv_ref,'^[^/]+')),
  P2 generated always as (to_number(regexp_substr(inv_ref,'/(\d+)/',1,1,null,1))),
  P3 generated always as (regexp_substr(inv_ref,'[^/]+$'))
);

In this case you can use standard predicates on those columns. Moreover, you can even create indexes on those columns.

Full test case:

CREATE TABLE YOUR_TABLE ( inv_ref ) AS
SELECT 'MX/3280/20' FROM DUAL UNION ALL
SELECT 'CT/3281/20' FROM DUAL UNION ALL
SELECT 'CT/3109/20' FROM DUAL UNION ALL
SELECT 'MX/3272/20' FROM DUAL UNION ALL
SELECT 'RF/3275/20' FROM DUAL UNION ALL
SELECT 'CX/3299/20' FROM DUAL UNION ALL
SELECT 'MT/3270/20' FROM DUAL UNION ALL
SELECT 'ACT/3270/20' FROM DUAL;

alter table your_table add (
  P1 generated always as (regexp_substr(inv_ref,'^[^/]+')),
  P2 generated always as (to_number(regexp_substr(inv_ref,'/(\d+)/',1,1,null,1))),
  P3 generated always as (regexp_substr(inv_ref,'[^/]+$'))
);

select * from your_table
where p1 IN ('CT', 'MX')
  and p2 BETWEEN 3270 and 3299;

Result:

MX/3280/20  MX        3280 20
CT/3281/20  CT        3281 20
MX/3272/20  MX        3272 20

To answer the question you need a `WHERE` clause on the final select like `WHERE p1 IN ('CT', 'MX') AND p2 BETWEEN 3270 and 3299`. — MT0, Jul 10 '20 at 09:22

Roberto Hernandez · Answer 3 · 2020-07-10T09:10:41.120

1

You could use

SQL> create table my_test ( inv_ref varchar2(100) ) ;

SQL> insert into my_test values ( 'MX/3280/20') ;

SQL> insert into my_test values ( 'CD/3281/20') ;

SQL> insert into my_test values ( 'CD/3109/20') ;

SQL> insert into my_test values ( 'MX/3272/20') ;

SQL> insert into my_test values ( 'RF/3275/20') ;

Table created.

SQL> SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL> SQL>

1 row created.

SQL> commit ;

Commit complete.

Two options ( in my case I use CD instead of CT ). This option will work as long as the strings are limited to the example. If you would have other combination it won't , as CD|MX means C or D or M or X ). See comments to the answer. @MTO, Thanks for your comments.

SQL> select * from my_test where regexp_like(inv_ref, '[CD|MX]/32[7-9][0-9]/20')

INV_REF
--------------------------------------------------------------------------------
MX/3280/20
CD/3281/20
MX/3272/20


SQL> select * from my_test where regexp_like(inv_ref, 'CD/32[7-9][0-9]/20') or 
regexp_like(inv_ref, 'MX/32[7-9][0-9]/20')

INV_REF
--------------------------------------------------------------------------------
MX/3280/20
CD/3281/20
MX/3272/20

edited Jul 10 '20 at 09:10

answered Jul 10 '20 at 08:45

Roberto Hernandez

8,231
3
14
43

The first option is wrong. `[CD|MX]` will match a single character that is either `C` or `D` or `|` or `M` or `X`. It looks like it succeeds because you have not anchored it to the start of the string and it can match the `D` or the `X` but it matches too much. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=cab7c93a685042dca6d9c9e233a86e8f) – MT0 Jul 10 '20 at 08:48
If I enclosed the combinations, "CD"|"MX" it should work, right ? – Roberto Hernandez Jul 10 '20 at 08:55
No, it will now match a single character that is either `"` or `C` or `D` or `"` or `|` or `"` or `M` or `X` or `"`. – MT0 Jul 10 '20 at 08:59
you are totally right. I will edit my answer to say that it would work as long as the combinations are limited. Thank you @MT0 – Roberto Hernandez Jul 10 '20 at 09:08

Popeye · Answer 4 · 2020-07-10T09:08:22.803

0

You can use the values separated by | (or) as follows:

select * from TA where regexp_like(inv_ref, '^(CT|MX)/32[7-9][0-9]/20');

db<>fiddle

edited Jul 10 '20 at 09:08

answered Jul 10 '20 at 08:38

Popeye

35,427
4
10
31

This is wrong. `[CT|MX]` will match a single character that is either `C` or `T` or `|` or `M` or `X`. It looks like it succeeds because you have not anchored it to the start of the string and it can match the `T` or the `X` but it matches too much. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=ab1c92aa37621674ef366dc7930446ee) – MT0 Jul 10 '20 at 08:45
Still matches too much [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c8e19be6247d58a2f412c68edb914937) – MT0 Jul 10 '20 at 09:00
Just use ^(CT|MX). [abc] is is a "character class" that means "any character from a,b or c" (a character class may use ranges, e.g. [a-d] = [abcd]) – Sayan Malakshinov Jul 10 '20 at 09:07

PL/SQL Regex search based on both characters and digits

4 Answers4