PL/SQL regexp_like filters

Question

I want to delete some tables and wrote this procedure:

    set serveroutput on
    declare
        type namearray is table of varchar2(50);
        total integer;
        name namearray;
    begin
        --select statement here ..., please see below
        total :=name.count;
        dbms_output_line(total);

        for i in 1 .. total loop
        dbms_output.put_line(name(i));
        -- execute immediate 'drop table ' || name(i) || ' purge';
    End loop;
    end;
    /

The idea is to drop all tables with table name having pattern like this:

ERROR_REPORT[2 digit][3 Capital characters][10 digits]
example: ERROR_REPORT16MAY2014122748

However, I am not able to come up with the correct regexp. Below are my select statements and results:

select table_name bulk collect into name from user_tables where regexp_like(table_name, '^ERROR_REPORT[0-9{2}A-Z{3}0-9{10}]');

The results included all the table names I needed plus ERROR_REPORT311AUG20111111111. This should not be showing up in the result.

The follow select statement showed the same result, which meant the A-Z{3} had no effect on the regexp.

select table_name bulk collect into name from user_tables where regexp_like(table_name, '^ERROR_REPORT[0-9{2}0-9{10}]');

My question is what would be the correct regexp, and what's wrong with mine?

Thanks,

Alex

arturro · Accepted Answer · 2017-01-09 21:56:52Z

1

Correct regex is

'^ERROR_REPORT[0-9]{2}[A-Z]{3}[0-9]{10}'

answered Jan 9, 2017 at 21:56

arturro

1,6061 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Casper · Accepted Answer · 2017-01-09 21:55:36Z

0

I think this regex should work:

^ERROR_REPORT[0-9]{2}[A-Z]{3}[0-9]{10}

However, please check the regex101 link. I've assumed that you need 2 digits after ERROR_REPORT but your example name shows 3.

answered Jan 9, 2017 at 21:55

Casper

1,44510 silver badges22 bronze badges

2 Comments

user1941319 Over a year ago

Thanks! I see. What about \d? Is \d the same as [0-9]?

user1941319 Over a year ago

Please ignore the question on \d.

Collectives™ on Stack Overflow

PL/SQL regexp_like filters

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related