Oracle Regular Expression Function
|
Metacharacter
Syntax |
Operator
Name |
Description |
|
. |
Any
Character: Dot |
Matches
any character |
|
+ |
One or
More: Plus Quantifier |
Matches
one or more occurrences of the preceding subexpression |
|
? |
Zero or
One: Question Mark Quantifier |
Matches
zero or one occurrence of the preceding subexpression |
|
* |
Zero or
More: Star Quantifier |
Matches
zero or more occurrences of the preceding subexpression |
|
{m} |
Interval:
Exact Count |
Matches
exactly m occurrences of the preceding subexpression |
|
{m,} |
Interval:
At Least Count |
Matches
at least m occurrences of the preceding subexpression |
|
{m,n} |
Interval:
Between Count |
Matches
at least m, but not more than n occurrences of the preceding subexpression |
|
[ … ] |
Matching
Character List |
Matches
any character in list … |
|
[^ … ] |
Non-Matching
Character List |
Matches
any character not in list … |
|
| |
Or |
Matches
either character each side of this symbol |
|
( … ) |
Subexpression
or Grouping |
Treat
expression … as a unit. The subexpression can be a string of literals or a
complex expression containing operators. |
|
\n |
Backreference |
Matches
the nth preceding subexpression, where n is an integer from 1
to 9. |
|
\ |
Escape
Character |
Treat
the subsequent metacharacter in the expression as a literal. |
|
^ |
Beginning
of Line Anchor |
Match
the subsequent expression only when it occurs at the beginning of a line. |
|
$ |
End of
Line Anchor |
Match
the preceding expression only when it occurs at the end of a line. |
|
[:upper:] |
[A-Z] |
uppercase
letters |
|
[:lower:] |
[a-z] |
lowercase
letters |
|
[:alpha:] |
[A-Za-z] |
upper-
and lowercase letters |
|
[:digit:] |
[0-9] |
digits |
|
[:xdigit:] |
[0-9A-Fa-f] |
hexadecimal
digits |
|
[:alnum:] |
[A-Za-z0-9] |
digits,
upper- and lowercase letters |
|
[:punct:] |
punctuation
(all graphic characters except letters and digits) |
|
|
[:blank:] |
[ \t] |
space
and TAB characters only |
|
[:space:] |
[
\t\n\r\f\v] |
blank
(whitespace) characters |
|
[:cntrl:] |
control
characters |
|
|
[:graph:] |
[^
[:cntrl:]] |
graphic
characters (all characters which have graphic representation) |
|
[:print:] |
[[:graph]
] |
graphic
characters and space |
REGEXP_LIKE:
The REGEXP_LIKE function searches a column for a
specified pattern. It’s used in a WHERE clause to
check if a column matches a pattern, and if it does,
then the row is included in the result set.
It’s similar to the LIKE condition, but allows for
regular expressions.
The syntax for the REGEXP_LIKE function is:
REGEXP_LIKE (source_string, pattern [,
match_parameter])
Example 1:
Below query will return phone number starting 3.3.4 digits like (981.983.1234)
Select phone
from emp
where regexp_like(phone,'\d{3}.\d{3}.\d{4}');
O/P:
=====
PHONE
=======
981.983.1234
834.768.5783
011.444.6789
101.201.3001
Example 2:
Below query will return phone numbers starting with 3.3.4 digits like
(981.983.1234)
Select phone
from emp
where
regexp_like(phone,'[[:digit:]]{3}.[[:digit:]]{3}.[[:digit:]]{4}');
O/P:
=====
PHONE
=======
981.983.1234
834.768.5783
011.444.6789
101.201.3001
Example 3:
Below exmaple will retrun ename's having any one of the below characters 'AY'
Select ename
from emp
where regexp_like(ename,'[:AK:]');
O/P:
====
ENAME
========
KING
BLAKE
ALLEN
Example 4:
'^' Power symbol will used when you want to return only name with first letter
A & K
Select ename
from emp
where regexp_like(ename,'^[:AK:]');
O/P:
====
ENAME
========
KING
ALLEN
ADAMS
Example 5:
Below Query will return names ranging from characters from A to C (A,B,C)
'^' Power symbol will used when you want to return
only name with first letter A,B and C
Select ename
from emp
where regexp_like(ename,'^[A-C]');
O/P:
====
ENAME
========
BLAKE
CLARK
ALLEN
ADAMS
Example 6:
'^' Power symbol outside the square braket ^[] will consider First letter
'^' Power symbol within [^] will display ename whose
first character is not A,B and C.
Select ename
from emp
where regexp_like(ename,'^[^A-C]');
O/P:
=====
ENAME
========
KING
JONES
SCOTT
FORD
REGEXP_SUBSTR
Function
The Oracle REGEXP_SUBSTR function allows you to search
for a string inside another string, using regular expressions.
It’s similar to the REGEXP_INSTR function, but instead
of returning the position of the string, it returns the substring. One of the
uses is to split a string into separate rows.
It extends the SUBSTR function but allows the user of
regular expressions.
The function returns a VARCHAR2 or CLOB data type,
depending on what has been provided as an input.
The syntax of the REGEXP_SUBSTR function is:
REGEXP_SUBSTR (
source_string, pattern [, position [, occurrence [,
match_parameter ] ] ])
Example 1:
Below query will retrun extract of first 3 digits
Select phone,regexp_substr(Phone,'^[0-9]{3}') Extract
from emp;
O/P:
=====
PHONE
EXTRACT
====================
981.983.1234 981
834.768.5783 834
011.444.6789 011
101.201.3001 101
Example 2:
This example finds a substring that matches two consecutive vowels.
SELECT ename,
REGEXP_SUBSTR(ename, '[A|E|I|O|U]{1,}') AS reg
FROM emp;
O/P:
=====
ENAME REG
=============
KING12 I
BLAKE A
CLARK A
JONES O
SMITH I
It returns the substring that was found, and NULL for
all other records.
Example 3:
This example finds all consecutive vowels in a string that are the same, and
returns NULL for those that don’t have consecutive vowels that are the same.
SELECT ename,
REGEXP_SUBSTR(ename, '([A|E|I|O|U])\1') AS reg
FROM emp;
O/P:
=====
ENAME REG
=============
KING12 null
SAMEER EE
KING null
BLAKE null
Example 4:
This example finds substrings that contain one or more digits.
SELECT ename,
REGEXP_SUBSTR(ename, '[[:digit:]]+') AS reg
FROM emp;
O/P:
=====
ENAME REG
=============
KING12 12
SAMEER null
KING null
BLAKE null
Example 5:
This example finds substrings that have a vowel followed by a letter from “A”
to “M”, starting from position 4.
SELECT job,
REGEXP_SUBSTR(job,'[A|E|I|O|U][A-M]',4) AS reg
FROM emp;
O/P:
=====
JOB REG
=============
CLERK null
PRESIDENT ID
MANAGER AG
REGEXP_INSTR
Function
The Oracle REGEXP_INSTR function lets you search a
string for a regular expression pattern, and
returns a number that indicates where the pattern was
found.
It’s similar to the Oracle INSTR function, but it
handles regular expressions where INSTR does not.
The syntax for the REGEXP_INSTR function is:
REGEXP_INSTR (
source_string, pattern [, position [, occurrence
[, return_option [, match_parameter [, sub_expression
] ] ] ] ])
Example 1:
Let's find the position of the first 't' character in a string.
SELECT REGEXP_INSTR ('TechOnTheNet is a great
resource', 't')
FROM dual;
Result: 12
This example will return 12 because it is performing a
case-sensitive search of 't'. Therefore, it skips the 'T' characters and finds
the first 't' in the 12th position.
Example 2:
If we wanted to include both 't' and 'T' in our results and perform a
case-insensitive search, we could modify our query as follows:
SELECT REGEXP_INSTR ('TechOnTheNet is a great
resource', 't', 1, 1, 0, 'i')
FROM dual;
Result: 1
Now because we have provide a start_position of 1, an
nth_appearance of 1, a return_option of 0, and a match_parameter of 'i', the
query will return 1 as the result. This time, the function will search for both
't' and 'T' values and return the first occurrence.
Example 3: The
REGEXP_INSTR function to match on a multi-character pattern.
SELECT REGEXP_INSTR ('The example shows how to use the
REGEXP_INSTR function', 'ow', 1, 1, 0, 'i')
FROM dual;
Result: 15
This example will return the first occurrence of 'ow'
in the string. It will match on the 'ow' in the word 'shows'.
Example 4:
To find the first occurrence of a pattern in a string.
SELECT REGEXP_INSTR ('TechOnTheNet', 'a|e|i|o|u', 1,
1, 0, 'i')
FROM dual;
Result: 2
This example will return 2 because it is searching for
the first occurrence of a vowel (a, e, i, o, or u) in the string.
Example 5:
The
search for the second occurrence of a pattern in a string.
SELECT REGEXP_INSTR ('TechOnTheNet', 'a|e|i|o|u', 1,
2, 0, 'i')
FROM dual;
Result: 5
This example will return 5 because it is searching for
the second occurrence of a vowel (a, e, i, o, or u) in the string.
REGEXP_REPLACE
Function
The Oracle REGEXP_REPLACE function is used to search a
string for a regular expression and replace it with other characters.
It’s an extension of the standard Oracle REPLACE
function, but REPLACE does not support regular expressions where REGEXP_REPLACE
does.
The syntax for this function is:
REGEXP_REPLACE (source_string, pattern
[, replace_string [, position [, occurrence [,
match_parameter ] ] ] ])
Example 1: REGEXP_REPLACE
function to replace the first word in a string.
SELECT REGEXP_REPLACE ('TechOnTheNet is a great
resource', '^(\S*)', 'CheckYourMath') FROM dual;
Result: 'CheckYourMath is a great resource'
This example will return 'CheckYourMath is a great
resource' because it will start the match at the beginning of the string as
specified by^ and then find the first
word as specified by (\S*). The function will then replace this first word with
'CheckYourMath'.
Example 2:
REGEXP_REPLACE function to match on a single digit character pattern.
SELECT REGEXP_REPLACE ('2, 5, and 10 are numbers in
this example', '\d', '#')FROM dual;
Result: '#, #, and ## are numbers in this example'
This example will replace all numeric digits in the
string as specified by \d. It will replace the occurrences with a # character.
Example 3: REGEXP_REPLACE
function with a table column to replace two digit numbers.
SELECT REGEXP_REPLACE (deptno, '(\d)(\d)', 'TBD') FROM
emp;
Result: TBD
TBD
TBD
TBD
In this example, we saw how to replace all two-digit
values from the deptno field in the emp table with the value 'TBD'.
Example 4:
To perform a case-insensitive search as follows:
SELECT REGEXP_REPLACE ('Anderson', 'a|e|i|o|u', 'G',
1, 0, 'i') FROM dual;
Result: 'GndGrsGn'
Now because we have provide a match_parameter of 'i',
the query will replace 'A' in the string. This time, the 'A' in 'Anderson' will
be found as a match. Notice also that we specified 0 as the 5th parameter so
that all occurrences would be replaced.
Example 5:
To replace the first occurrence of a pattern in a string.
SELECT REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u',
'Z', 1, 1, 'i') FROM dual;
Result: 'TZchOnTheNet'
This example will replace the second character ('e')
in 'TechOnTheNet' because it is replacing the first occurrence of a vowel (a,
e, i, o, or u) in the string.
REGEXP_COUNT
Function
The Oracle REGEXP_COUNT function finds the number of
times a pattern occurs in a particular string.
It returns an integer which indicates the number of
times it was found. If no matches are found, it returns 0.
The REGEXP_COUNT function is a new function in Oracle
11g. It works in a similar way to REGEXP_INSTR.
The syntax of this function is:
REGEXP_COUNT (source_string, pattern [, position [,
match_parameter ] ] ] )
Example 1:
To count the number of times the character 't' appears in a string.
SELECT REGEXP_COUNT ('TechOnTheNet is a great
resource', 't')
FROM dual;
Result: 2
This example will return 2 because it is counting the
number of occurrences of 't' in the string. Since we did not specify a
match_parameter value, the REGEXP_COUNT function will perform a case-sensitive
search which means that the 'T' characters will not be included in the count.
Example 2:
To include both 't' and 'T' in our results and perform a case-insensitive
search, we could modify our query as follows:
SELECT REGEXP_COUNT ('TechOnTheNet is a great
resource', 't', 1, 'i') FROM dual;
Result: 4
Now because we have provide a start_position of 1 and
a match_parameter of 'i', the query will return 4 as the result. This time,
both 't' and 'T' values would be included in the count.
Example 3:
The
REGEXP_COUNT function to match on a multi-character pattern.
SELECT REGEXP_COUNT ('The example shows how to use the
REGEXP_COUNT function', 'the', 1, 'i') FROM dual;
Result: 2
This example will return the number of times that the
word 'the' appears in the string. It will perform a case-insensitive search so
it will return 2.
Example 4:
This example look for | pattern. The | pattern is used like an "OR"
to specify more than one alternative.
SELECT REGEXP_COUNT ('Anderson', 'a|e|i|o|u')FROM
dual;
Result: 2
This example will return 2 because it is counting the
number of vowels (a, e, i, o, or u) in the string 'Anderson'. Since we did not
specify a match_parameter value, the REGEXP_COUNT function will perform a
case-sensitive search which means that the 'A' in 'Anderson' will not be
counted.
Example 5:
To count no. of vowels (a,e,i,o,u) in the column ename.
SELECT Ename,REGEXP_COUNT (ename, 'a|e|i|o|u', 1, 'i')
AS total
FROM emp;
This example will return number of vowels in each name
from ename column of emp table.
No comments:
Post a Comment