2

I have simple question. why would :

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

not work?

1
  • For the same reason select 'A'='A' from dual; 'does not work'? (something similar works on other RDBMSs - eg postgres) Sep 12, 2012 at 14:57

2 Answers 2

2

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.

0

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;
/
3
  • 1
    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. Sep 12, 2012 at 14:28
  • 1
    @Justin that would be great info to edit into your answer too? Sep 12, 2012 at 14:56
  • @JackDouglas - Good point. Done. Sep 12, 2012 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.