By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t42 (id number);
select regexp_substr(table_name, '\d+$') result
from user_tables
where regexp_like(table_name, '\d+$')
| RESULT |
|---|
| 42 |
declare
splitstring varchar2(30);
begin
EXECUTE IMMEDIATE 'select regexp_substr(table_name, ''\d+$'') result
from user_tables
where regexp_like(table_name, ''\d+$'')'
into splitstring;
dbms_output.put_line('splitstring: ' || splitstring);
end;
/
1 rows affected
dbms_output:
splitstring: 42
declare
splitstring varchar2(30);
begin
EXECUTE IMMEDIATE q'^select regexp_substr(table_name, '\d+$') result
from user_tables
where regexp_like(table_name, '\d+$')^'
into splitstring;
dbms_output.put_line('splitstring: ' || splitstring);
end;
/
1 rows affected
dbms_output:
splitstring: 42
declare
splitstring varchar2(30);
begin
select regexp_substr(table_name, '\d+$') result
into splitstring
from user_tables
where regexp_like(table_name, '\d+$');
dbms_output.put_line('splitstring: ' || splitstring);
end;
/
1 rows affected
dbms_output:
splitstring: 42