How to fuzzy query a list?-CodePudding

SELECT *

FROM table

WHERE column_one LIKE IN ('%one%', '%two%');

This will be error with : SQL Error [936] [42000]: ORA-00936: missing expression

How to implement this kind of fuzzy query?

CodePudding user response：

Of course as commented the usage of OR is the correct way, though a bit verbose.

The most close option to your approach is the usage of regexp_like with a pattern allowing more alternatives.

Example

with tab as (
select '...one..' column_one from dual union all
select '.two.,..' column_one from dual union all
select '.three..' column_one from dual)
select *
from tab
where  regexp_like(column_one,'(one|two)'); 

COLUMN_O
--------
...one..
.two.,..

CodePudding user response：

You may generate lookup table and check it for every input row with exists predicate.

create table t as
  select
    level as id
    , decode(mod(level, 7), 1,'A', 'B')
      || dbms_random.string('i', 10) as val
  from dual
  connect by level < 16

with lkp(val) as (
  select *
  from table(sys.odcivarchar2list(
    'A%',
    '%F%'
  ))
)
select *
from t
where exists (
  select 1
  from lkp
  where t.val like lkp.val
)

ID | VAL        
-: | :----------
 1 | AJHCIHZIVQY
 2 | BQCYACBBLFS
 3 | BVATBIEQFEF
 4 | BHEFYAUUENF
 7 | BFPZVWRWTFP
 8 | AGSKVEXPFSV
11 | BFSJXDRGAWV
14 | BFVXCSQWUQT
15 | AFJPPPHDTKM

db<>fiddle here