Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have simple question. why would :

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

not work?

share|improve this question
    
For the same reason select 'A'='A' from dual; 'does not work'? (something similar works on other RDBMSs - eg postgres) –  Jack Douglas Sep 12 '12 at 14:57

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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;
/
share|improve this answer
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. –  Justin Cave Sep 12 '12 at 14:28
1  
@Justin that would be great info to edit into your answer too? –  Jack Douglas Sep 12 '12 at 14:56
    
@JackDouglas - Good point. Done. –  Justin Cave Sep 12 '12 at 14:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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