REGEXP_REPLACE

View All Scripts Login to Run Script

Script Name REGEXP_REPLACE
Description This script demonstrates the use of the REGEXP_REPLACE function to search a string value for a regular expression and then replace the expression.
Area SQL General / Functions
Referenced In Database SQL Language Reference
Contributor Oracle
Created Friday November 20, 2015

Statement 1
This statement uses the REGEXP_REPLACE function to replace all numbers within a given string with an empty string, thus removing the numbers. The second parameter of REGEXP_REPLACE indicates the regular expression that will be replaced. The third parameter represents the replace string which in this examples is an empty string.
Remove numbers from strings
```
WITH strings AS (   
  SELECT 'abc123' s FROM dual union all   
  SELECT '123abc' s FROM dual union all   
  SELECT 'a1b2c3' s FROM dual   
)   
  SELECT s "STRING", regexp_replace(s, '[0-9]', '') "MODIFIED_STRING"  
  FROM strings
```
```
STRING MODIFIED_STRING
abc123 abc
123abc abc
a1b2c3 abc

3 rows selected.
```
Statement 2
This statement uses the REGEXP_REPLACE function to replace only the first numeric occurrence within a given string with an empty string. Any other numeric values that occur in the string are left as is. Specifying 1 as the fifth parameter indicates that only the first occurrence must be replaced.
Remove the first occurrence of numbers from strings
```
WITH strings AS (   
  SELECT 'abc123' s from DUAL union all   
  SELECT '123abc' s from DUAL union all   
  SELECT 'a1b2c3' s from DUAL   
)   
  SELECT s "STRING", REGEXP_REPLACE(s, '[0-9]', '', 1, 1) "MODIFIED_STRING"  
  FROM   strings
```
```
STRING MODIFIED_STRING
abc123 abc23
123abc 23abc
a1b2c3 ab2c3

3 rows selected.
```
Statement 3
This statement uses the REGEXP_REPLACE function to replace only the second numeric occurrence within a given string with an empty string. Any other numeric values that occur in the string are left as is. Specifying 2 as the fifth parameter indicates that only the second occurrence must be replaced.
Remove the second occurrence of numbers in strings
```
WITH strings AS (   
  SELECT 'abc123' s from DUAL union all   
  SELECT '123abc' s from DUAL union all   
  SELECT 'a1b2c3' s from DUAL   
)   
  SELECT s "STRING", REGEXP_REPLACE(s, '[0-9]', '', 1, 2) "MODIFIED_STRING"  
  FROM   strings
```
```
STRING MODIFIED_STRING
abc123 abc13
123abc 13abc
a1b2c3 a1bc3

3 rows selected.
```

STRING	MODIFIED_STRING
abc123	abc
123abc	abc
a1b2c3	abc

STRING	MODIFIED_STRING
abc123	abc23
123abc	23abc
a1b2c3	ab2c3

STRING	MODIFIED_STRING
abc123	abc13
123abc	13abc
a1b2c3	a1bc3

Statement 4

This statement replaces multiple spaces within a string with a single space. The second parameter of the REGEXP_REPLACE function, ' {2,}', represents two or more consecutive spaces.

Convert multiple spaces into a single space

WITH strings AS (   
  SELECT 'Hello  World' s FROM dual union all   
  SELECT 'Hello        World' s FROM dual union all   
  SELECT 'Hello,   World  !' s FROM dual   
)   
  SELECT s "STRING", regexp_replace(s, ' {2,}', ' ') "MODIFIED_STRING"  
  FROM   strings


STRING MODIFIED_STRING
Hello  World Hello World
Hello        World Hello World
Hello,   World  ! Hello, World !

3 rows selected.

STRING	MODIFIED_STRING
Hello World	Hello World
Hello World	Hello World
Hello, World !	Hello, World !

Statement 5

This statement uses the REGEXP_REPLACE function to convert a camel case string to a string containing lowercase words separated by an underscore. A camel case string is a compound word in which each word begins with a capital letter.

Convert camel case string to lowercase with underscores between words

WITH strings as (   
  SELECT 'AddressLine1' s FROM dual union all   
  SELECT 'ZipCode' s FROM dual union all   
  SELECT 'Country' s FROM dual   
)   
  SELECT s "STRING",  
         lower(regexp_replace(s, '([A-Z0-9])', '_\1', 2)) "MODIFIED_STRING"  
  FROM strings


STRING MODIFIED_STRING
AddressLine1 address_line_1
ZipCode zip_code
Country country

3 rows selected.

STRING	MODIFIED_STRING
AddressLine1	address_line_1
ZipCode	zip_code
Country	country

Statement 6

This statement converts a date value using the yyyy-mm-dd format to a date value that uses the dd.mm.yyyy format.

Convert yyyy-mm-dd date formats to dd.mm.yyyy

WITH date_strings AS (   
  SELECT  '2015-01-01' d from dual union all   
  SELECT '2000-12-31' d from dual union all   
  SELECT '900-01-01' d from dual   
)   
  SELECT d "STRING",   
         regexp_replace(d, '([[:digit:]]+)-([[:digit:]]{2})-([[:digit:]]{2})', '\3.\2.\1') "MODIFIED_STRING"  
  FROM date_strings


STRING MODIFIED_STRING
2015-01-01 01.01.2015
2000-12-31 31.12.2000
900-01-01 01.01.900

3 rows selected.

Statement 7
This statement uses the REGEXP_REPLACE function to remove all letters from a string an replace it with '1'. The second parameter indicates the regular expression pattern that is searched for in the string value S. The third parameter specifies the replace string. The fourth parameter specifies the position at which the search begins in the source string. The fifth parameter indicates the occurrence of the replace operation. This example uses 0 which specifies that all occurrences must be replaced. The last parameter changes the default matching behavior of REGEXP_REPLACE. 'i' specifies case-insensitive matching and 'c' specifies case-sensitive matching.
Remove all letters from a string
```
WITH strings as (   
  SELECT 'NEW YORK' s FROM dual union all   
  SELECT 'New York' s FROM dual union all   
  SELECT 'new york' s FROM dual   
)   
  SELECT s "STRING",  
        regexp_replace(s, '[a-z]', '1', 1, 0, 'i') "CASE_INSENSITIVE",  
        regexp_replace(s, '[a-z]', '1', 1, 0, 'c') "CASE_SENSITIVE",  
        regexp_replace(s, '[a-zA-Z]', '1', 1, 0, 'c') "CASE_SENSITIVE_MATCHING"  
  FROM  strings
```
```
STRING CASE_INSENSITIVE CASE_SENSITIVE CASE_SENSITIVE_MATCHING
NEW YORK 111 1111 NEW YORK 111 1111
New York 111 1111 N11 Y111 111 1111
new york 111 1111 111 1111 111 1111

3 rows selected.
```

STRING	MODIFIED_STRING
2015-01-01	01.01.2015
2000-12-31	31.12.2000
900-01-01	01.01.900

STRING	CASE_INSENSITIVE	CASE_SENSITIVE	CASE_SENSITIVE_MATCHING
NEW YORK	111 1111	NEW YORK	111 1111
New York	111 1111	N11 Y111	111 1111
new york	111 1111	111 1111	111 1111

Additional Information

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