17. COMMIT;
18.
19. SELECT ssn_col
20. from ssn_test
21. WHERE regexp_like(ssn_col,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$');
CREATE TABLE mytest (c1 VARCHAR2(20), CHECK (REGEXP_LIKE(c1, '^[[:alpha:]]+$'))); Identify SSN Thanks: Byron Bush HIOUG CREATE TABLE ssn_test ( ssn_col VARCHAR2(20)); INSERT INTO ssn_test VALUES ('111-22-3333'); INSERT INTO ssn_test VALUES ('111=22-3333'); INSERT INTO ssn_test VALUES ('111-A2-3333'); INSERT INTO ssn_test VALUES ('111-22-33339'); INSERT INTO ssn_test VALUES ('111-2-23333'); INSERT INTO ssn_test VALUES ('987-65-4321'); COMMIT; SELECT ssn_col from ssn_test WHERE regexp_like(ssn_col,'^[0-9]{3}-[0-9]{2}-[0-9]{4}$'
REGEXP_REPLACE
Syntax REGEXP_REPLACE(
Looks for the pattern xxx.xxx.xxxx and reformats pattern to (xxx) xxx-xxxx col testcol format a15
col result format a15
1. SELECT testcol, REGEXP_REPLACE(testcol,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
2. '(\1) \2-\3') RESULT
3. FROM test
4. WHERE LENGTH(testcol) = 12;
SELECT testcol, REGEXP_REPLACE(testcol,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') RESULT FROM test WHERE LENGTH(testcol) = 12;
Put a space after every character
1. SELECT testcol, REGEXP_REPLACE(testcol, '(.)', '\1 ') RESULT
2. FROM test WHERE testcol like 'S%';
SELECT testcol, REGEXP_REPLACE(testcol, '(.)', '\1 ') RESULT FROM test WHERE testcol like 'S%';
Replace multiple spaces with a single space
1. SELECT REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ') RESULT
2. FROM dual;
SELECT REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ') RESULT FROM dual
Insert a space between a lower case character followed by an upper case character
1. SELECT REGEXP_REPLACE('George McGovern', '([[:lower:]])([[:upper:]])', '\1 \2') CITY
2. FROM dual;
SELECT REGEXP_REPLACE('George McGovern', '([[:lower:]])([[:upper:]])', '\1 \2') CITY FROM dual;
Replace the period with a string (note use of '\')
1. SELECT REGEXP_REPLACE('We are trying to make the subject easier.','\.',' for you.') REGEXT_SAMPLE
2. FROM dual;
SELECT REGEXP_REPLACE('We are trying to make the subject easier.','\.',' for you.') REGEXT_SAMPLE FROM dual;
REGEXP_SUBSTR
Syntax REGEXP_SUBSTR(source_string, pattern[, position [, occurrence[, match_parameter]]])
Searches for a comma followed by one or more occurrences of non-comma characters followed by a comma
1. SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') RESULT
2. FROM dual;
SELECT REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') RESULT FROM dual;
Look for http:// followed by a substring of one or more alphanumeric characters and optionally, a period (.) col result format a50
1. SELECT REGEXP_SUBSTR('Go to http://www.oracle.com/products and click on database',
2. 'http://([[:alnum:]]+\. ){3,4}/ ') RESULT
3. FROM dual;
SELECT REGEXP_SUBSTR('Go to http://www.oracle.com/products and click on database', 'http://([[:alnum:]]+\. ){3,4}/ ') RESULT FROM dual;
Extracts try, trying, tried or tries
SELECT REGEXP_SUBSTR('We are trying to make the subject easier.','tr(y(ing) |(ied)|(ies))')
FROM dual;
Extract the 3rd field treating ':' as a delimiter SELECT REGEXP_SUBSTR('system/pwd@orabase:1521:sidval',
'[^:]+', 1, 3) RESULT
FROM dual;
Extract from string with vertical bar delimiter
1. CREATE TABLE regexp (