Oracle View loop on specific tables only

I got many tables of which some end with digits (date).

Due to technical reasons I had to create a view on every table (just a workaround)

The current state is something like:

begin
  for i in (select table_name 
              from   user_tables 
              where  table_name like 'XXX_%'
             )
  loop
    execute immediate 'CREATE VIEW '||replace(i.TABLE_NAME,'XXX_','YYY_')||' AS SELECT * FROM '||i.TABLE_NAME;
  end loop;             
end;
/

All it does is creating views for every table which begins with ‘XXX_’. Its almost what I need.

But now, sometimes tablenames look like “XXX_tablename_20210302”, which is a manual backup.

Its not always 8 digits (date), sometimes shorter, soemtimes longer numbers. I would like to avoid all tables, which do have numbers in the end of the tablename (from right to left till first “_” checkup, its a number maybe?)

Does anyone know how to solve it?

Im kind of stuck here.

Answer

You can use regular expressions with regexp_like:

create table xxx_t ( c1 int );
create table xxx_t_20210401 ( c1 int );

select table_name from user_tables
where  regexp_like ( table_name, '^XXX_.*[^0-9]+$' );

TABLE_NAME   
XXX_T         

This finds all the tables that

• Start with XXX_ XXX_
• Followed by any characters .*
• That do not end with a digit [^0-9]+$