How to/Possible to Generate String of Table Based Row Values for CONTAINS Search String?

Question

So ideally I'd like to keep this within a query, but if it's not possible, I suppose a package could work as I have no webapp level to work with.

What I'd like to do is this, for an Oracle DB, create/run a query in the where clause such that for each row in a table with one attribute where all rows have substrings with wildcards are stored it adds on to the search string for contains. Since to my knowledge, you can't really do loops within queries, one would need a cursor, but I've never used cursors. Here is a more visual representation of what I'm trying to do (abet with loop logic):

Table1
Attribute: firstname

John
Joe
Jane
Josephine

Table2
Attribute: substrings

%se%
%h%i%

Where by constraint there is guaranteed to always be at least one row

Pseudo Query

SELECT 
  table1.firstname
FROM
  table1
WHERE CONTAINS(table1.firstname, '"table2.row1"
  IF(count(table2.substrings) > 1)
    FOR table2.row = 2 TO count(table2.substrings)
      (
      + " OR row.substrings"
      )
 ', 1) > 0

(CONTAINS syntax based on Is there a combination of "LIKE" and "IN" in SQL?)

score 1 · Accepted Answer · answered Jul 24 '13 at 19:56

1

I am not exactly sure what do you want to get, but I think these simple examples might help.

select * 
from table1 t1
where exists(
    select 1 from table2 t2
    where t1.firstname like t2.attribute
);


select t1.*,
       ( select listagg( ''''||t2.attribute||'''', ' OR ' ) WITHIN GROUP (order by t2.attribute )
         from table2 t2
         where t1.firstname like t2.attribute
       ) CONTAINS_argument
from table1 t1

Here is an SQLFiddle demo of these queries.

answered Jul 24 '13 at 19:56

krokodilko

35,300
7
55
79

First query is what I needed and after some fiddling got it to work in my environment. I tried the second one out of curiosity because I've never hear of listagg before and got a FROM not where expected error on the second line, but it's not that big of a deal considering it's #1 that I needed. DB was running Oracle 9, so I suspect it's a legacy syntax issue. – Kurt Wagner Jul 25 '13 at 18:40

score 0 · Answer 2 · answered Jul 24 '13 at 21:07

Some variants (according to my understanding of the question), based on usage of collections and multiset casts:

SQLFiddle

All strings with matched patterns:

select
  t1.firstname,
  cast( multiset(
    select t2.attribute
    from table2 t2 
    where t1.firstname like t2.attribute
  ) as sys.ODCIVarchar2List)              pattern_list
from 
  table1 t1
;

All patterns with strings matched pattern:

select 
  t2.attribute,
  cast( multiset(
    select t1.firstname
    from table1 t1 
    where t1.firstname like t2.attribute
  ) as sys.ODCIVarchar2List)               word_list
from
  table2 t2
;

Construct patterns collection on the fly:

with table2 as (
  select '%se%' Attribute from dual union
  select '%h%i%' from dual   
)
select
  t1.firstname,
  cast( multiset(
    select t2.attribute
    from table2 t2 
    where t1.firstname like t2.attribute
  ) as sys.ODCIVarchar2List)               pattern_list
from 
  table1 t1;

Filter only matched:

select 
  firstname, 
  (select count(1) from table(pattern_list)) cnt,
  pattern_list
from (
  select
    t1.firstname,
    cast( multiset(
      select t2.attribute
      from table2 t2 
      where t1.firstname like t2.attribute
    ) as sys.ODCIVarchar2List)             pattern_list
  from 
    table1 t1
)
where (select count(1) from table(pattern_list)) > 0;

And so on.

How to/Possible to Generate String of Table Based Row Values for CONTAINS Search String?

2 Answers2