Concatenate values of field depending on text selection Oracle SQL

Question

I have a table a_x with the columns A and B, column A is in CLOB format, column B number(10):

A                            | B                                    
-----------------------------|-------
atveroeosipsumloremipsumdolor| 1
stetclitakasdtest            | 2
noseatakimataatveroeosipsum  | 3
loremipsumdolor              | 4
consetetursadipscingelitr    | 5

I would like to produce this table, in order to find out, which ID goes with certain substrings:

A                            | IDs                                    
-----------------------------|-------
atveroeosipsum               | 1,3
test                         | 2
stetclitakasd                | 2
noseatakimata                | 3
loremipsumdolor              | 1,4
consetetursadipscingelitr    | 5

I tried the following code:

create table a_y 
as
select a 
  from a_x where a contains('atveroeosipsum', 'test'
                  , 'stetclitakasd', 'noseatakimata'
                  , 'loremipsumdolor', 'consetetursadipscingelitr')

alter table a_y
add ids varchar2(2000); 

The code is not working because of 00920. 00000 - "invalid relational operator". I think it is not possible to search for text in CLOB format. How can I produce the second table, when column A is in varchar format?

UPDATE: The code from mathguy works. I wanted to use a table called table_expressions, which contains the desired expressions. I created this table, which contains only one column (column A of the "result table").

The modified code from mathguy:

create table a_y 
  as 
  with 
  input_strings ( a ) as ( 
select column_value from table_expressions 
  ) 
select t2.a, listagg(t1.b, ',') within group (order by t1.b) 
 as ids from a_x t1 join input_strings t2 on t1.a like '%' || t2.a || '%' 
 group by t2.a 

See also question XMLAGG

Answer 1

The correct operator for string comparisons is LIKE. Note that it works for CLOBs, not just for VARCHAR2.

In the example below I create the table of input strings on the fly using one particular method. There are several other methods - use whichever you are familiar with.

with
     a_x ( a, b ) as (
       select to_clob('atveroeosipsumloremipsumdolor'), 1 from dual union all
       select to_clob('stetclitakasdtest')            , 2 from dual union all
       select to_clob('noseatakimataatveroeosipsum')  , 3 from dual union all
       select to_clob('loremipsumdolor')              , 4 from dual union all
       select to_clob('consetetursadipscingelitr')    , 5 from dual
     ),
     input_strings ( str ) as (
       select column_value
       from   table ( sys.odcivarchar2list ( 'atveroeosipsum', 'test', 'stetclitakasd',
                                             'noseatakimata', 'loremipsumdolor',
                                             'consetetursadipscingelitr'
                                           )
                    )                    
     )
select   t2.str, listagg(t1.b, ',') within group (order by t1.b) as ids
from     a_x t1 
         join input_strings t2 on t1.a like '%' || t2.str || '%'
group by t2.str
;

STR                        IDS
-------------------------  ---
atveroeosipsum             1,3
consetetursadipscingelitr  5
loremipsumdolor            1,4
noseatakimata              3
stetclitakasd              2
test                       2