REGEXP_COUNT - Case Sensitive and Insensitive Matching

View All Scripts Login to Run Script

Script Name REGEXP_COUNT - Case Sensitive and Insensitive Matching
Description Counting the number of times a specified pattern occurs in a name. Performs both case sensitive and case insensitive matching.
Area SQL General / Functions
Referenced In Database SQL Language Reference
Contributor Oracle
Created Monday October 05, 2015

Statement 1
The statement creates a table with one field - empName.
Create a new table regexp_temp
```
CREATE TABLE regexp_temp(empName varchar2(20))
```
```
Table created.
```
Statement 2
The statement inserts a row with value for - employee name.
Inserting rows into the regexp_temp table
```
INSERT INTO regexp_temp (empName) VALUES ('John Doe')
```
```
1 row(s) inserted.
```
Statement 3
The statement inserts a row with values for - employee name.
Inserting rows into the regexp_temp table
```
INSERT INTO regexp_temp (empName) VALUES ('Jane Doe')
```
```
1 row(s) inserted.
```
Statement 4
The statement queries employee name column "empName" and looks for the lowercase of character "e" starting from the first character in the string indicated by "1". The lowercase is determined by "c" in the REGEXP_COUNT function. Watch the resulting columns and the character count.
Query the table column and count a specific character
```
SELECT empName, REGEXP_COUNT(empName, 'e', 1, 'c') "CASE_SENSITIVE_E" From regexp_temp
```
```
EMPNAME CASE_SENSITIVE_E
John Doe 1
Jane Doe 2

2 rows selected.
```
Statement 5
The statement queries employee name column "empName" and looks for the lowercase of character "o" starting from the first character in the string indicated by "1". The lowercase is determined by "c" in the REGEXP_COUNT function. Watch the resulting columns and the character count.
Query the table column and count a specific character
```
SELECT empName, REGEXP_COUNT(empName, 'o', 1, 'c') "CASE_SENSITIVE_O" From regexp_temp
```
```
EMPNAME CASE_SENSITIVE_O
John Doe 2
Jane Doe 1

2 rows selected.
```
Statement 6
The statement queries employee name column "empName" and looks for the lowercase or uppercase of character "E" starting from the first character in the string indicated by "1". The case insensitivity is determined by "i" in the REGEXP_COUNT function. Watch the resulting columns and the character count and compare it with the previous uppercase count result.
Query the table column and count a specific character
```
SELECT empName, REGEXP_COUNT(empName, 'E', 1, 'i') "CASE_INSENSITIVE_E" From regexp_temp
```
```
EMPNAME CASE_INSENSITIVE_E
John Doe 1
Jane Doe 2

2 rows selected.
```
Statement 7
The statement queries employee name column "empName" and looks for the lowercase or uppercase of string "do" starting from the first character in the name string indicated by "1". The case insensitivity is determined by "i" in the REGEXP_COUNT function. Watch the resulting columns and the string count.
Query the table column and count a specific string
```
SELECT empName, REGEXP_COUNT(empName, 'do', 1, 'i') "CASE_INSENSITIVE_STRING" From regexp_temp
```
```
EMPNAME CASE_INSENSITIVE_STRING
John Doe 1
Jane Doe 1

2 rows selected.
```
Statement 8
The statement queries employee name column "empName" and looks for the lowercase of the string "an" starting from the first character in the string indicated by "1". The lowercase is determined by "c" in the REGEXP_COUNT function. Watch the resulting columns and the string count.
Query the table column and count a specific string in uppercase
```
SELECT empName, REGEXP_COUNT(empName, 'an', 1, 'c') "CASE_SENSITIVE_STRING" From regexp_temp
```
```
EMPNAME CASE_SENSITIVE_STRING
John Doe 0
Jane Doe 1

2 rows selected.
```
Statement 9
Drops the table.
Drop the table
```
DROP TABLE regexp_temp
```
```
Table dropped.
```

EMPNAME	CASE_SENSITIVE_E
John Doe	1
Jane Doe	2

EMPNAME	CASE_SENSITIVE_O
John Doe	2
Jane Doe	1

EMPNAME	CASE_INSENSITIVE_E
John Doe	1
Jane Doe	2

EMPNAME	CASE_INSENSITIVE_STRING
John Doe	1
Jane Doe	1

EMPNAME	CASE_SENSITIVE_STRING
John Doe	0
Jane Doe	1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database