Using Regular expression to replace where clause in select statement

Question

Basically I would like to replace the where clause from the below select statement using regex. (here there are three condition; 1. Owner name 2. table to be included 3. table to be excluded)

SELECT *
FROM all_tables
WHERE owner = 'XXXXXXXX'
   AND (table_name LIKE '%_\_A' ESCAPE '\'
     OR table_name LIKE '%_\_B' ESCAPE '\'
     OR table_name LIKE '%_\_C' ESCAPE '\')
   AND (table_name NOT LIKE statement for (P|Q)(R|S)D(.*)(_D$)')

What I have done so far?

In below select statement I wrote REGEXP_LIKE to get table name ending with A or B or C and same NOT REGEXP_LIKE for those table names which follow a pattern like (P|Q)(R|S)*(_D)) and needs to be excluded.

I know this way REGEXP_LIKE and NOT REGEXP_LIKE wont work, Can anyone please help me to find one single regex for the above 2 (regexp_like and not regexp_like). Thank you very much

SELECT table_name
FROM all_tables
WHERE owner = 'XXXXXXXX'
   AND REGEXP_LIKE (table_name, '(.*)((A|B|C)$)', 'i')
   AND WHERE NOT REGEXP_LIKE(table_name,'^(P|Q)(R|S)D(.*)(_D$)', 'i');

A question about your second regex: are the parentheses supposed to be literal characters, or are you using the parentheses to group expressions? Thanks. — Bob Jarvis - Слава Україні, Apr 30 '19 at 11:41
The parantheses used above are for grouping like either P or Q can come at first place in above example and similarly it shoyld only end with _D and hence grouped them. — user10914967, May 02 '19 at 04:29

score 0 · Accepted Answer · answered Apr 30 '19 at 06:12

0

You might have included an extra WHERE

SELECT table_name
FROM all_tables
WHERE owner = 'XXXXXXXX' AND
REGEXP_LIKE(table_name,'(.*)((A|B|C)$)', 'i') AND
NOT REGEXP_LIKE(table_name,'^(P|Q)(R|S)D(.*)(_D$)', 'i');

answered Apr 30 '19 at 06:12

Jim Jimson

2,368
3
17
40

Thanks Jim, You have got the correct point. I was using one more "where" which was not needed. All good now. Thanks again. – user10914967 May 03 '19 at 02:44
Sometimes it takes a second set of eyes :) – Jim Jimson May 03 '19 at 03:05

score 0 · Answer 2 · answered Apr 30 '19 at 11:40

0

If I understand what you're trying to do the following should work:

SELECT table_name
FROM all_tables
WHERE owner = 'XXXXXXXX' AND
      REGEXP_LIKE (table_name, '[ABC]$', 'i') AND
      NOT REGEXP_LIKE(table_name,'^(P|Q)(R|S)D(.*)(_D$)', 'i')

The first regex says "Accept any name with characters in the class [ABC] just before the end of the line". I used your second regex as given.

Best of luck.

answered Apr 30 '19 at 11:40

Bob Jarvis - Слава Україні

48,992
9
77
110

Thanks Bob for your comment, however the first regex which you modified could not be used here as the letters A B C which we have mentioned above are just for example, in realiy it ends with a combination of them may be like AB or 2A something like that. Can you please help me in this case. I am mainly facing problem in making all three where clauses at one place. – user10914967 May 02 '19 at 04:27
If the question you write doesn't match your actual problem you can hardly be surprised when the answers you get don't solve your problem. I suggest you write another question which clearly explains the problem you've got and the difficulties you're encountering. However, the regular expression `[ABC]$` will match strings which end in either A or B or C, such as `'AB'` or `'2A'` or `'THE_RAIN_IN_SPAIN_FALLS_MAINLY_IN_THE_C'`. [dbfiddle here](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=dd9170359bf745ed132285e06d858b6e). Perhaps you should study a tutorial on regular expressions. – Bob Jarvis - Слава Україні May 02 '19 at 11:34
Thanks Bob, I would have a look there, as of now "where" has resolved the issue, however, I will have a check what you have mentioned and update you. Thanks agin. – user10914967 May 06 '19 at 02:55

Using Regular expression to replace where clause in select statement

2 Answers2