Oracle Quick Tips #1

LIKE & REGEXP_LIKE

LIKE:-

The Oracle LIKE condition allows wildcards to be used in the WHERE clause of a SELECT,INSERT,UPDATE or DELETE statement.

REGEXP_LIKE:-

The Oracle REGEXP_LIKE condition allows you to perform regular expression matching in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.

From the above statements we can conclude that there is no much difference in the functionality of a LIKE and REGEXP_LIKE conditions.

But when we use these operators to find/match multiple values/words the repetition can be avoided by using REGEXP_LIKE.

LIKE Example:

WHERE (COLUMN LIKE '%value1%' OR COLUMN LIKE '%value2% OR....)

REGEXP_LIKE Example: Much Easier

• The pipe symbol '|' inside the regular expression acts as the OR operator and the parenthesis () form the subexpression that is needed for that OR.

WHERE REGEXP_LIKE(COLUMN,'(value1|value2|value3...)')

OR & ANY

OR:-

The Oracle OR operator is a logical operator that combines Boolean expressions and returns true if one of the expressions is true.

ANY:-

The Oracle ANY operator is used to compare a value to a list of values or result set returned by a subquery.

But instead of writing multiple OR operators the ANY operator can be used to avoid repetition.

OR Example:

SELECT * 
  FROM table 
WHERE COLUMN_1 = 'VALUE1'  
   OR COLUMN_1 = 'VALUE2'  
   OR COLUMN_1 = 'VALUE3'  

ANY Example: Much Easier

SELECT * 
  FROM table 
WHERE COLUMN_1 = ANY('VALUE1','VALUE2','VALUE3')  

Happy CODING!!!

THANK YOU:)