0

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

yPennylane
  • 760
  • 1
  • 9
  • 27

1 Answers1

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
  • How would you change the code in order to use the column `A` of the table `a_x` as input without creating the table. I tried: `create table a_y as with a_x (a), 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 ;` but it showed the error `00905. 00000 - "missing keyword"`. The table `table_expressions` contains column `A` from table `a_y` in my question – yPennylane Jun 26 '17 at 08:23
  • @yPennylane - In your case, the table `a_x` exists, so you don't need to create it on the fly. So remove it from the query and leave everything else exactly as it is. `create table a_y as with input_strings ( a ) ...` - there should be no mention of `a_x (a)` and the comma after that should be removed too. –  Jun 26 '17 at 12:59
  • I tried: `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 ; ` but I get error `"table or view does not exist"`. – yPennylane Jun 26 '17 at 13:06
  • @yPennylane - what is `table_expressions`? I don't see that in my answer OR in your original post. If you had a question but asked a different one, and you are unable to transfer between the two, that's your problem - but that has nothing to do with your question or the solution I provided. In any case: the error message tells you exactly WHICH table or view does not exist. I assume it is pointing to `table_expressions`, or is it something else? –  Jun 26 '17 at 13:13
  • The second table in my post is the table_expressions. – yPennylane Jun 26 '17 at 13:47
  • @yPennylane - The second table in your post is the desired RESULT, the table you want to create. You can't possibly think you can refer to the table you want to create in the statement that creates it. In any case, none of this has anything to do with the ability to search in a CLOB column. If you have a different (and totally unrelated) question you should ask it in another thread - this is not a free consulting site. I'm done helping you on this. –  Jun 26 '17 at 13:51
  • column `A` of the second table of my question is column `A` of `table_expressions`. You were right: I didn't create the table_expressions. I fixed the problem and now it works. I hope it will work with the real data, too. – yPennylane Jun 26 '17 at 13:57
  • mathguy, Could you edit your answer? I edited my question. Thank you. – yPennylane Jun 28 '17 at 12:28
  • @yPennylane - so, the original problem is solved. You have a follow-up question, regarding LISTAGG resulting in a string that's too long. You did the right thing when you posted it as a NEW question. (Actually that wasn't exactly the *right* thing; a quick Google search would show that this is a very common question, with many good answers on SO already. I marked your NEW question that way, in its own thread). My answer shouldn't be edited - it answers your original question. If I start changing it, it won't help other users in the future, who have your first problem (but not the second). –  Jun 28 '17 at 12:33
  • ok. But I tried to use the XMLAGG function. And can't figure out, why it doesn't work. I'm pretty new to SQL. – yPennylane Jun 28 '17 at 12:34
  • @yPennylane - I'll give it a try and post an answer in the other thread (or others may have done so already). In any case, that's a separate question. –  Jun 28 '17 at 12:38