Is it possible to use pattern matching to select columns from Oracle SQL db?

A SQL query always returns a fixed, predefined set of columns. What you ask for requires dynamic SQL: that is, run a query that gathers the column names, save the results in a variable, and use it to build the actual query to execute.

If this is a one-time query, then I would recommend just running the following query:

select 'select ' 
    || listagg('"' || column_name || '"', ', ') within group (order by column_id)
    || ' from mytable' as sql_query
from all_tab_columns 
where owner = 'MYSCHEMA' and table_name = 'MYTABLE' and column_name like 'PERCENT_%';

This generates the query you want, that you can then copy/paste and run independently.

Note that I added a filter on the name of the schema (which is required to uniquely identify a table in the database). I also sorted the columns by their position in the table.

On the other hand if you want something entirely dynamic, then you can, for example, build a function that will return a cursor holding the query results.

create function myfunction return sys_refcursor
as
    v_sql varchar(1000);
    v_cursor sys_refcursor;
begin
    select 'select ' 
        || listagg('"' || column_name || '"', ', ') within group (order by column_id)
        || ' from mytable' as sql_query
    into v_sql
    from all_tab_columns 
    where table_name = 'MYTABLE' and column_name like 'PERCENT_%';
        
    open v_cursor for v_sql;
    return v_cursor;
end;
/

Then, you have to decide how to use the cursor.

In 18c and above, you can create a polymorphic table function to dynamically select columns based on patterns. In 12c and below, you need to use Oracle data cartridge to create dynamic SQL in SQL. Both solutions require PL/SQL, but the end results look and behave just like regular SQL. In practice, you likely want to solve these kinds of dynamic problems in an application layer instead of the database. For example, if you're building Excel spreadsheets, most Oracle-Excel APIs allow passing in a query as a string.

18c and above - Polymorphic Table Function

First, create a package that identifies which columns from the table to pass through the SQL statement, and which to ignore. There's a lot of unusual syntax with polymorphic table functions, so the below code won't make much sense at first glance.

create or replace package percent_cols as
    function percent_cols(tab in table) return table pipelined row polymorphic using percent_cols;
    function describe(tab in out dbms_tf.table_t) return dbms_tf.describe_t;
end percent_cols;
/

create or replace package body percent_cols as
    function describe (tab in out dbms_tf.table_t) return dbms_tf.describe_t
    as
    begin
        for i in 1 .. tab.column.count() loop
            if tab.column(i).description.name like '%PERCENT\_%' escape '\' then
                tab.column(i).pass_through := true;
            else
                tab.column(i).pass_through := false;
            end if;
        end loop;

        return null;
    end;
end percent_cols;
/

Create some sample tables to test the package:

create table test1(a number, percent_1 number);
insert into test1 values(1, 1);

create table test2(percent_1 number, percent_2 number);
insert into test2 values(2, 2);

create table test3(a number);
insert into test3 values(3);

The below sample queries only show the PERCENT% columns. You may need to add some exception handling for tables that don't have any matching columns.

SQL> select * from percent_cols(test1);

 PERCENT_1
----------
         1

SQL> select * from percent_cols(test2);

 PERCENT_1  PERCENT_2
---------- ----------
         2          2

SQL> select * from percent_cols(test3);
select * from percent_cols(test3)
              *
ERROR at line 1:
ORA-30732: table contains no user-visible columns

12c and below - Oracle data cartridge

Data cartridge lets us extend Oracle in many interesting ways, but the interface is difficult to work with. Instead of creating your own PL/SQL types and functions, you might want to start with my open source project.

This solution requires more code than the polymorphic table function, but it has the advantage of keeping more of the logic in regular SQL - you can see a single data dictionary query inside the table function, instead of hiding it behind PL/SQL.

select * from table(method4.dynamic_query(
    q'[
        select 'select '||listagg(column_name, ',') within group (order by column_id)||' from '||table_name v_sql
        from all_tab_columns
        where table_name = 'TEST2'
            and column_name like 'PERCENT_%'
        group by table_name
    ]'
));

 PERCENT_1  PERCENT_2
---------- ----------
         2          2