Convert CLOB column data translated to NUMBER field

Question

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);

You write: Col1 CLOB data in this column is ('27,88,100,25,26'). Pay attention - this is ONE value. You write where Col2 not in (27,88,100,25,26);. Pay attention - this is a LIST of values. Convert your CSV string to rowset. For example, you may add square brackets converting CSV to JSON ARRAY, and apply JSON_TABLE() function... — Akina
@Akina Can u please explain with an example how can I do this conversation in sqldeveloper — Risha
You could use dynamic SQL but that's a bit messy and potentially dangerous; plus if you're using a CLOB that suggests you expect a lot of values, and in() is limited to 1000 values. Otherwise you need to tokenize the string value into individual elements. Roughly how many rows are involved in each table, and if there are multiple rows in clob_tab how are they related to abc rows? And which version of Oracle are you using? — Alex Poole
the values i will be expecting in the subquery are max 10-15 values(numbers). — Risha
I use neither OracleDatabase nor SQLDeveloper. An example of JSON_TABLE() usage as it is applied in MySQL for CSV parsing may be found, for example, there. I think it can be easily adopted (or maybe even used as-is). — Akina

Alex Poole Alex Poole · Accepted Answer · 2020-08-05T10:47:19

There are various ways to tokenzise your string; this uses regular expressions:

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), '$[*]' columns n number path '$') x
);

db<>fiddle

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

Convert CLOB column data translated to NUMBER field

2 Answers