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