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

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