Convert CLOB column data translated to NUMBER field

I am using ORACLE SQL Developer. I have a table Clob_tab haveing column with data type as: Col1 CLOB data in this column is ('27,88,100,25,26')

I want to use this value from this column as subquery into another query: select * from Abc where Col2 not in (select Col1 from Clob_tab ); DATATYPE of Col2 is NUMBER. I want output as : select * from Abc where Col2 not in (27,88,100,25,26);

Is it possible?

I have tried multiple things which are not working like: Convert blob to varchar2 : dbms_lob.substr(MY_FIELD_BLOB_TYPE) Convert varchar2 to Number using regex_replace: select cast(regexp_replace(Col1 , '[^0-9]+', '') as number) from Clob_tab . Using regex_repalce all the commas are disappearing and I am getting 27881002526. I don't want this number. I want the numbers separated by commas.

None of them gives/translates my query to this form:

select * from Abc where Col2 not in (27,88,100,25,26);

Solution

There are various ways to tokenzise your string; this uses regular expressions (but is likely to be slow; see comments below):

with cte (n) as (
  select to_number(regexp_substr(col1, '(.*?)(,|$)', 1, level, null, 1))
  from clob_tab
  connect by level < regexp_count(col1, '(.*?)(,|$)')
)
select *
from abc
where col2 not in (
  select n from cte
);

This uses XMLTable to treat the values as a sequence and extract them:

select *
from abc
where col2 not in (
  select to_number(x.column_value)
  from clob_tab
  cross join xmltable (col1) x
);

Depending on your Oracle version you could probably do something similar with JSON rather than XML:

select *
from abc
where col2 not in (
  select x.n
  from clob_tab
  cross join json_table (json_array(col1 format json returning clob), '$[*]' columns n number path '$') x
);

fiddle

It's worth trying various approaches and comparing performance with your data.

As @MT0 reported in comments (included here in case those are deleted):

The first (hierarchical) query has huge performance problems when the number of terms get large (especially if CLOB_TAB has more than one row). For a larger data-set this gives an indication of performance fiddle Summary: regular expressions were so slow that the run failed when they were included, XML took 0.8s, JSON and sub-string matching both took < 0.1s. (Although more data is needed for an accurate comparison)

Updated my fiddle to use a string splitting method from this answer that avoids regular expressions and uses recursive queries (rather than hierarchical) and the string splitting appears to perform better than XML but worse than JSON or sub-string matching fiddle