Oracle regular expression regexp_like

Question

I have simple question. why would :

select regexp_like( (select 'https://www.hotmail.com' from dual) ,'(f|ht)tps?:') from dual ;

not work?

Answer 1

What do you expect this query to do?

regexp_like is a regular expression version of the LIKE statement so it makes sense to use it in the same sorts of places that you would use a LIKE. You wouldn't try to directly SELECT the result of a LIKE statement. You could, however, embed the regexp_like in a case statement. For example

SQL> ed
Wrote file afiedt.buf

  1  select (case when regexp_like( 'https://www.hotmail.com' ,'(f|ht)tps?:')
  2               then 'true'
  3               else 'false'
  4           end) does_it_match
  5*   from dual
SQL> /

DOES_
-----
true

regexp_like is a function that returns a boolean. Oracle SQL, however, does not support the boolean data type so you cannot directly SELECT the result of the function just like you couldn't SELECT a function you write that returns a boolean.

Answer 2

Because for some strange reason, although regexp_like looks like a function on a first glance, it is not a function in reality, but is a condition (an integral part of SQL syntax).

But, more confusingly, it is a function if used in PL/SQL:

DECLARE
   x BOOLEAN;
BEGIN
   x := regexp_like( 'a', 'b' ) ;
END;
/