Oracle - Sub query within Regexp_Like

Question

Oracle: There is a "CUSTOMER" table and "EXCEPTION_KEYWORDS" table. I want to select the customers where the name contains any keyword from the Exception_keywords table.

I tried using a sub query inside Regexp_Like but getting error:

ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row"

My query:

SELECT * FROM CUSTOMER 
WHERE REGEXP_LIKE (STD_NAME,(Select KEYWORD from EXCEPTION_KEYWORDS),'i');

Note: I need solution only using Regexp.

Answer 1

Maybe an exists query using LIKE and lower()

SELECT * FROM CUSTOMER  c 
    WHERE  EXISTS ( SELECT 1 FROM 
         EXCEPTION_KEYWORDS e 
 WHERE lower(c.STD_NAME) like '%'||lower(e.KEYWORD)||'%' );

Answer 2

Check existance

SELECT * 
FROM CUSTOMER 
WHERE EXISTS (select null from EXCEPTION_KEYWORDS where REGEXP_LIKE (STD_NAME,KEYWORD ,'i'));

Answer 3

Another approach would be using regexp_like() with listagg() function :

SELECT * 
  FROM CUSTOMER 
 WHERE REGEXP_LIKE (STD_NAME,
 (SELECT LISTAGG(KEYWORD,'|') WITHIN GROUP (ORDER BY KEYWORD) list 
    FROM EXCEPTION_KEYWORDS),'i');

Answer 4

You can directly JOIN those tables and use regexp_like() within the ON clause :

SELECT * 
  FROM CUSTOMER 
  JOIN EXCEPTION_KEYWORDS 
    ON REGEXP_LIKE (STD_NAME,KEYWORD,'i');

Demo