Skip to content

Oracle-Regular Expression

Learn about pattern matching and pattern to be searched for within a longer piece of text.

Regular Expression

A sequence of symbols and characters expressing a string or pattern to be searched for within a longer piece of text.

How to write Regular Expression?

To write a pattern matching string we need some special symbols.

  1. + represents one or more characters
  2. * represents zero or more characters
  3. {n} represents n characters
  4. {n,} represents at least n times.
  5. {n,m} represents at least “n” times and at most “m” times.
  6. ^ represents matching of first character in the string
  7. $ represents matching of last character in the string
  8. | represents either OR or
  9. \S represents Matching a non-white-space character
  10. \s represents Matching a white-space character
  11. ‘^ ‘ represents Matching a white-space character
  12. \D Matches a non-digit character.
  13. \d Matches a digit character
  14. \w Matches a word character.
  15. \W Matches a non-word character.

For Example consider the below names in the table , for extracting the data that match the pattern

ENAME
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
  1. Find the names that starts with A.
   REGEXP(ENAME,'^A')

   OUTPUT: 
    ALLEN
    ADAMS

2.Find the names that ends the N.

   REGEXP(ENAME,'N$')
   
   OUTPUT: ALLEN
	   MARTIN

3.Find the names that contains A.

   REGEXP(ENAME,'A')

   OUTPUT: ALLEN
	   WARD
	   MARTIN
     	   BLAKE
	   CLARK
 	   ADAMS
	   JAMES

4.Find the names that contains ER .

   REGEXP(ENAME,'ER')

   OUTPUT: TURNER
	   MILLER

5.Find the names that starts with A or S

   REGEXP(ENAME,'^[AS]')   OR  REGEXP(ENAME,'^(A|S)')

   OUTPUT: SMITH
           ALLEN
	   SCOTT
	   ADAMS

6.Find the names that starts with M and second letter should be either A or I

   REGEXP(ENAME,'^M(A|I)')

   OUTPUT: MARTIN
	   MILLER

8. Find the names that contains the sub-string With M followed by either A or I

   REGEXP(ENAME,'^M(A|I)')

   OUTPUT: SMITH
	   MARTIN
	   MILLER


Published inOracle PL SQL