8.
9. SELECT *
10. FROM test
11. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{3}');
12.
13. SELECT *
14. FROM test
15. WHERE REGEXP_LIKE(testcol, '[[:lower:]]{5}');
SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:lower:]]'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:lower:]]{2}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:lower:]]{3}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:lower:]]{5}');
Printable Characters
1. SELECT *
2. FROM test
3. WHERE REGEXP_LIKE(testcol, '[[:print:]]{5}');
4.
5. SELECT *
6. FROM test
7. WHERE REGEXP_LIKE(testcol, '[[:print:]]{6}');
8.
9. SELECT *
10. FROM test
11. WHERE REGEXP_LIKE(testcol, '[[:print:]]{7}');
SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:print:]]{5}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:print:]]{6}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:print:]]{7}');
Punctuation
1. TRUNCATE TABLE test;
2.
3. SELECT *
4. FROM test
5. WHERE REGEXP_LIKE(testcol, '[[:punct:]]');
TRUNCATE TABLE test; SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:punct:]]');
Spaces
1. SELECT *
2. FROM test
3. WHERE REGEXP_LIKE(testcol, '[[:space:]]');
4.
5. SELECT *
6. FROM test
7. WHERE REGEXP_LIKE(testcol, '[[:space:]]{2}');
8.
9. SELECT *
10. FROM test
11. WHERE REGEXP_LIKE(testcol, '[[:space:]]{3}');
12.
13. SELECT *
14. FROM test
15. WHERE REGEXP_LIKE(testcol, '[[:space:]]{5}');
SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]{2}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]{3}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:space:]]{5}')
Upper Case
1. SELECT *
2. FROM test
3. WHERE REGEXP_LIKE(testcol, '[[:upper:]]');
4.
5. SELECT *
6. FROM test
7. WHERE REGEXP_LIKE(testcol, '[[:upper:]]{2}');
8.
9. SELECT *
10. FROM test
11. WHERE REGEXP_LIKE(testcol, '[[:upper:]]{3}');
SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:upper:]]'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:upper:]]{2}'); SELECT * FROM test WHERE REGEXP_LIKE(testcol, '[[:upper:]]{3}');
Values Starting with 'a%b'
1. SELECT testcol
2. FROM test
3. WHERE REGEXP_LIKE(testcol, '^ab*');
SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^ab*');
'a' is the third value
1. SELECT testcol
2. ROM test WHERE REGEXP_LIKE(testcol, '^..a.');
SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^..a.');
Contains two consecutive occurances of the letter 'a' or 'z'
1. SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '([az])\1', 'i');
SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '([az])\1', 'i')
Begins with 'Ste' ends with 'en' and contains either 'v' or 'ph' in the center
1. SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^Ste(v|ph)en$');
SELECT testcol FROM test WHERE REGEXP_LIKE(testcol, '^Ste(v|ph)en$');
Use a regular expression in a check constraint
1. CREATE TABLE mytest (c1 VARCHAR2(20),
2. CHECK (REGEXP_LIKE(c1, '^[[:alpha:]]+$')));
3. Identify SSN
4.
5. Thanks: Byron Bush HIOUG
6.
7.
8. CREATE TABLE ssn_test (
9. ssn_col VARCHAR2(20));
10.
11. INSERT INTO ssn_test VALUES ('111-22-3333');
12. INSERT INTO ssn_test VALUES ('111=22-3333');
13. INSERT INTO ssn_test VALUES ('111-A2-3333');
14. INSERT INTO ssn_test VALUES ('111-22-33339');
15. INSERT INTO ssn_test VALUES ('111-2-23333');
1