2

I have a table with Description where it can have multiple words, I need to compare with a set of words ( set of words are output from another query using LISTAGG function) to check if the word is existing in Description column or not.

For example if my Description has text like below and compare with word PIN only rows 3,4 to return.

  1. SHIPPING ITEM
  2. PIPING ITEM
  3. CLAMPING PIN
  4. SPINDLE PIN
Prakash Kavilapati
  • 21
  • 2

1 Answers1

1

You can use regular expressions. For your example:

select *
from (select 'SHIPPING ITEM' as col from dual union all
      select 'SPINDLE PIN' from dual
     ) x
where regexp_like(' ' || col || ' ', '\WPIN\W')

If you have a string such as 'PIN,BALL' being passed in, you can construct the expression that you want:

where regexp_like(' ' || col || ' ', 
                  '\W' || replace('PIN,BALL', ',', '\W|\W') || '\W'
                 )

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you, it is almost working. If I have to get Keywords like 'PIN,BALL' from another query when I use a sub query or a variable in that place it is throwing me error saying : java.sql.SQLException: ORA-12722: regular expression internal error. Please help in this. – Prakash Kavilapati Oct 04 '19 at 06:48
  • seems Replace is not taking more characters .. I have 24 key words which results complete text with more than 150 Characters, so it is not working. – Prakash Kavilapati Oct 04 '19 at 07:25
  • @PrakashKavilapati . . . I added a db<>fiddle. It works when I try it. – Gordon Linoff Oct 04 '19 at 10:45
  • I tried there as well .. I have added more Key words like below and it fails to run if you add another word after ROBOT, which means there is a limit of characters for each version of Oracle, my version doesn't work after 125, https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=6df0a83184ed930e31f45c6dc9444bfa – Prakash Kavilapati Oct 04 '19 at 11:34
  • @PrakashKavilapati . . . There is a limit, but it is 2000 bytes for character strings. A regular expression is limited to 512 bytes. – Gordon Linoff Oct 04 '19 at 11:48
  • Does that mean I can't use this query when I have Keywords to compare more than 512 bytes ? Please help me if you have any other way of writing this query – Prakash Kavilapati Oct 04 '19 at 11:51
  • @PrakashKavilapati . . . Yes it would imply that you cannot use regular expressions for this purpose. Actually, you might want to consider a full text search or splitting the original text into separate words. The latter would be a win from a performance perspective as well. – Gordon Linoff Oct 04 '19 at 12:12