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?

• Jack Douglas: For the same reason select 'A'='A' from dual; 'does not work'? (something similar works on other RDBMSs - eg postgres)

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;
/

• Justin Cave: regexp_like is a function. Because it is a function that returns a boolean, however, and SQL does not support the boolean data type, you cannot directly SELECT the result of the function just like you couldn't SELECT a function you write that returns a boolean.
• Jack Douglas: @Justin that would be great info to edit into your answer too?
• Justin Cave: @JackDouglas - Good point. Done.