Home >> FAQs/Tutorials >> Oracle DBA FAQ

Oracle DBA FAQ - Understanding SQL Basics

By: FYIcenter.com

Part:   1  2  3  4  5  6  

(Continued from previous part...)

How To Use IN Conditions?

An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE.

SELECT CASE WHEN 3 IN (1,2,3,5) THEN 
  'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE 

SELECT CASE WHEN 3 NOT IN (1,2,3,5) THEN 
  'TRUE' ELSE 'FALSE' END FROM DUAL;
FALSE   

SELECT CASE WHEN 'Y' IN ('F','Y','I') THEN 
  'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

How To Use LIKE Conditions?

LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition:

• '_' is used in the pattern to match any one character.
• '%' is used in the pattern to match any zero or more characters.
• ESCAPE clause is used to provide the escape character in the pattern.

The following script provides you some good pattern matching examples:

SELECT CASE WHEN 'FYICenter.com' LIKE '%Center%' 
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE   

SELECT CASE WHEN 'FYICenter.com' LIKE '%CENTER%' 
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
  -- Case sensitive by default
FALSE

SELECT CASE WHEN 'FYICenter.com' LIKE '%Center_com' 
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE   

SELECT CASE WHEN '100% correct' LIKE '100\% %' ESCAPE '\' 
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE   

How To Use Regular Expression in Pattern Match Conditions?

If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE().

SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*fyi.*',
  'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*com$',
  'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '^F.*','i')
  THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

Part:   1  2  3  4  5  6