Dynamic LIKE clause or something like LIKE IN

I have requirement like below:

There are two tables i)TB_VALUES and ii)TB_KEYWORDS.

Here are some sample data for

TB_VALUES:

ID Description
00001 Oracle is better than SQL Server
00001 Python is good
00001 Why should we use oracle
00002 pl/sql guidelines
00002 python tutorial

TB_KEYWORDS:

ID Keywords
00001 Oracle
00001 SQL
00002 SQL

We have to now find the data from TB_VALUES , the Description of those data should contain the keyword. That means for 00001 the description should contain “SQL” or “Oracle” (case insensitive) and for 00002 the description should contain “SQL“(case insensitive) . Our output should be like this :

ID Description
00001 Oracle is better than SQL Server
00001 Why should we use oracle
00002 pl/sql guidelines

For that we have to write something like this:

SELECT V.ID,V.DESCRIPTION FROM TB_VALUES V JOIN TB_KEYWORDS K ON V.ID = K.ID
WHERE UPPER(V.DESCRIPTION) LIKE '%SQL%' OR UPPER(V.DESCRIPTION) LIKE '%ORACLE%' AND V.ID = 00001     
UNION ALL
SELECT V.ID,V.DESCRIPTION FROM TB_VALUES V JOIN TB_KEYWORDS K ON V.ID = K.ID
WHERE UPPER(V.DESCRIPTION) LIKE '%SQL%' AND V.ID = 00002  

But I want to do it dynamically.

I can achieve this in PL/SQL but I want output as a view.

Answer

Join + INSTR might help.

SQL> with
  2  -- sample data
  3  tb_values (id, description) as
  4    (select 1, 'oracle is better than SQL server' from dual union all
  5     select 1, 'python is Good'                   from dual union all
  6     select 1, 'Why should we use Oracle'         from dual union all
  7     select 2, 'pl/sql guidelines'                from dual union all
  8     select 2, 'python tutorial'                  from dual
  9    ),
 10  tb_keywords (id, keywords) as
 11    (select 1, 'Oracle' from dual union all
 12     select 1, 'SQL'    from dual union all
 13     select 2, 'SQL'    from dual
 14    )
 15  -- query you need
 16  select distinct
 17    k.id,
 18    v.description
 19  from tb_keywords k join tb_values v on v.id = k.id
 20    and instr(lower(v.description), lower(k.keywords)) > 0
 21  order by k.id, v.description;

        ID DESCRIPTION
---------- --------------------------------
         1 oracle is better than SQL server
         1 Why should we use Oracle
         2 pl/sql guidelines

SQL>

---

If it were a view (as you mentioned), then – based on those tables – it might look like this:

SQL> create or replace view v_key as
  2  select distinct
  3    k.id,
  4    k.keywords,
  5    v.description
  6  from tb_keywords k join tb_values v on v.id = k.id
  7    and instr(lower(v.description), lower(k.keywords)) > 0;

View created.

SQL>
SQL> select * from v_key
  2  where lower(keywords) = 'sql';

        ID KEYWOR DESCRIPTION
---------- ------ --------------------------------
         1 SQL    oracle is better than SQL server
         2 SQL    pl/sql guidelines

SQL>