REGEXP_LIKE() available in oracle but throws error when run on SQL Developer

Question

Running the below code throws an error when run on SQL Developer:

SELECT REGEXP_LIKE('CTCP AYD 0404370713 M', 'CTCH.*[0-9]/{0,1}.*', 'i') FROM DUAL;

Error message:

ORA-00904: "REGEXP_LIKE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 40 Column: 8

Not sure why this is throwing up error when REGEXP_LIKE() function is available in oracle.

Any views from Oracle db gurus?

Answer 1

regexp_like is a condition. Use it in the where clause or other places with boolean comparisons - e.g. case expressions:

SELECT * FROM DUAL
WHERE  REGEXP_LIKE('CTCP AYD 0404370713 M', 'CTCH.*[0-9]/{0,1}.*', 'i');

no rows selected

SELECT CASE
  WHEN REGEXP_LIKE('CTCP AYD 0404370713 M', 'CTCH.*[0-9]/{0,1}.*', 'i') 
  THEN 'like this'
  ELSE 'not like this'
END rl
FROM DUAL;

RL           
-------------
not like this