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

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
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

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