REGEXP_LIKE
REGEXP_LIKE(x, pattern [, match_option]) searches x for the regular expression defined in the pattern parameter.
The optional match_option may:
- 'c' does case-sensitive matching (this is the default)
- 'I' does case-insensitive matching
- 'n' uses the match-any-character operator
- 'm' treats x as a multiple line
The metacharacters list:
| Metacharacters | Meaning |
|---|---|
| \ | Specify the escape sequence |
| \d | Digit character |
| \D | Non-digit character |
| \w | Word character |
| \W | Non-word character |
| \s | Whitespace character |
| \S | Non-whitespace character |
| \A | Matches only at the beginning of a string or before a newline character at the end of a string |
| \Z | Matches only at the end of a string |
| ^ | Matches the position at the start of the string. |
| $ | Matches the position at the end of the string. |
| * | Matches the preceding character zero or more times. |
| + | Matches the preceding character one or more times. |
| ? | Matches the preceding character zero or one time. |
| *? | Matches the preceding pattern element 0 or more times |
| +? | Matches the preceding pattern element 1 or more times |
| ?? | Matches the preceding pattern element 0 or 1 time |
| {n} | Matches a character exactly n times, where n is an integer. |
| {n,} | Matches the preceding pattern element at least n times |
| {n,m} | Matches a character at least n times and at most m times, where n and m are both integers. |
| . | Matches any single character except null. |
| (pattern) | A subexpression that matches the specified pattern. |
| x|y | Matches x or y, where x and y are one or more characters. war|peace matches war or peace. |
| [abc] | Matches any of the enclosed characters. |
| [a-z] | Matches any character in the specified range. |
| [:alphanum:] | matches alphanumeric characters 0-9, A-Z, and a-z. |
| [:alpha:] | matches alphabetic characters A-Z and a-z. |
| [:blank:] | matches space or tab. |
| [:digit:] | matches digits 0-9. |
| [:graph:] | matches non-blank characters. |
| [:lower:] | matches lowercase alphabetic characters a-z. |
| [:print:] | is similar to [:graph:] except [:print:] includes the space character. |
| [:punct:] | matches punctuation characters .,"`, and so on. |
| [:space:] | matches all whitespace characters. |
| [:upper:] | matches all uppercase alphabetic characters A-Z. |
| [:xdigit:] | matches characters permissible in a hexadecimal number 0-9, A-F, and a-f. |
| [..] | Matches one collation element, like a multicharacter element. |
| [==] | Specifies equivalence classes. |
| \n | A backreference to an earlier capture, where n is a positive integer. |
Example:
| Example | Description |
|---|---|
| \n | matches the newline character |
| \\ | matches \ |
| \( | matches ( |
| ^A | matches if A is the first character in the string. |
| $B | matches if B is the last character in the string. |
| f*d | matches flood, food, and so on. |
| fo+d | matches fod, food, and so on. |
| fo?d | matches fd and fod only. |
| fo{2}d | matches food. |
| fo{2,3}d | matches food and foood only. |
| [ab]bc | matches abc and bbc. |
| [a-c]bc | matches abc, bbc, and cbc. |
The following sql use REGEXP_LIKE to match the year of hiredate in 1981, 1982, and 1983.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
HIREDATE DATE);
INSERT INTO EMP VALUES (1, 'SMITH', TO_DATE('17-DEC-1985', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (2, 'ALLEN', TO_DATE('20-FEB-1980', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (3, 'WARD', TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (4, 'JONES', TO_DATE('2-APR-1982', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (5, 'MARTIN',TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));
select empno, ename
from emp
where REGEXP_LIKE(to_CHAR(hiredate,'YYYY'),'^198[1-3]$');
EMPNO ENAME
--------- ----------
3 WARD
4 JONES
5 MARTIN
Employee's name starts with M and ignore the case:
SQL> select empno, ename
2 from emp
3 where REGEXP_LIKE(ename,'^m','i');
EMPNO ENAME
---------- ----------
5 MARTIN