character class< Oracle >
SQL> SELECT REGEXP_SUBSTR( 'aBcD1234', '[[:ALNUM:]]+' ) AS RESULT FROM DUAL;
SELECT REGEXP_SUBSTR( 'aBcD1234', '[[:ALNUM:]]+' ) AS RESULT FROM DUAL
*
ERROR at line 1:
ORA-12729: invalid character class in regular expression
< PostgreSQL >
postgres=# SELECT REGEXP_MATCHES( 'aBcD1234', '[[:ALNUM:]]+' ) AS RESULT;
ERROR: invalid regular expression: invalid character class
< MySQL >
mysql> SELECT REGEXP_SUBSTR( 'aBcD1234', '[[:ALNUM:]]+' ) AS RESULT;
+----------+
| RESULT |
+----------+
| aBcD1234 |
+----------+
1 row in set (0.00 sec)
※ case sensitive한 Oracle과 PostgreSQL은, 문자 클래스를 대문자로 표기하면 에러처리한다.
※ 반면, case insensitive한 MySQL은 문자 클래스도 대문자로 표기 가능하다.
SQL> SELECT REGEXP_SUBSTR( 'aBcD1234', '[[:alnum:]]+' ) AS RESULT FROM DUAL;
RESULT
--------------------
aBcD1234
SQL> SELECT REGEXP_SUBSTR( 'aBcD1234', '[[:alpha:]]+' ) AS RESULT FROM DUAL;
RESULT
--------------------
aBcD
SQL> SELECT REGEXP_SUBSTR( 'a b', 'a[[:blank:]]b' ) AS RESULT FROM DUAL;
RESULT
------------------------------
a b
SQL> SELECT REGEXP_SUBSTR( 'a'||CHR(32)||'b', 'a[[:blank:]]b' ) AS RESULT FROM DUAL;
RESULT
------------------------------
a b
SELECT REGEXP_SUBSTR( 'a'||CHR(0)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
a b
SELECT REGEXP_SUBSTR( 'a'||CHR(1)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(2)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(3)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(4)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(5)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(6)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(7)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(8)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
b
SQL> SELECT REGEXP_SUBSTR( 'a'||CHR(9)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
a b
SELECT REGEXP_SUBSTR( 'a'||CHR(10)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
a
b
SELECT REGEXP_SUBSTR( 'a'||CHR(11)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
a
b
SELECT REGEXP_SUBSTR( 'a'||CHR(12)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
a
b
SELECT REGEXP_SUBSTR( 'a'||CHR(13)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
b
SELECT REGEXP_SUBSTR( 'a'||CHR(14)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(15)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(16)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(17)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(18)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SQL> SELECT REGEXP_SUBSTR( 'a'||CHR(19)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(20)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(21)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(22)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(23)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(24)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(25)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(26)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(27)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(28)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SQL> SELECT REGEXP_SUBSTR( 'a'||CHR(29)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(30)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(31)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SELECT REGEXP_SUBSTR( 'a'||CHR(32)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
NULL
SQL> SELECT REGEXP_SUBSTR( 'a'||CHR(127)||'b', 'a[[:cntrl:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
ab
SQL> SELECT REGEXP_SUBSTR( 'aBcD1234', '[[:digit:]]+' ) AS RESULT FROM DUAL;
RESULT
--------------------
1234
SQL> SELECT REGEXP_SUBSTR( 'Right Now!', '[[:graph:]]+', 1, 1 ) AS RESULT FROM DUAL;
RESULT
------------------------------
Right
SQL> SELECT REGEXP_SUBSTR( 'Right Now!', '[[:graph:]]+', 1, 2 ) AS RESULT FROM DUAL;
RESULT
------------------------------
Now!
SQL> SELECT REGEXP_SUBSTR( 'aBcD1234', '[[:lower:]]+', 1, 1 ) AS RESULT FROM DUAL;
RESULT
--------------------
a
SQL> SELECT REGEXP_SUBSTR( 'aBcD1234', '[[:lower:]]+', 1, 2 ) AS RESULT FROM DUAL;
RESULT
--------------------
c
SQL> SELECT REGEXP_SUBSTR( 'Right Now!', '[[:print:]]+' ) AS RESULT FROM DUAL;
RESULT
------------------------------
Right Now!
SQL> SELECT I1, REGEXP_SUBSTR( I1, '[[:punct:]]+' ) AS RESULT FROM T1;
I1 RESULT
--------------------------------------------- --------------------
!"#$%&'()*+,-./ !"#$%&'()*+,-./
:;<=>?@ :;<=>?@
[\]^_` [\]^_`
{|}~ {|}~
SQL> SELECT REGEXP_SUBSTR( 'a'||CHR(9)||'b', 'a[[:space:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
a b
SELECT REGEXP_SUBSTR( 'a'||CHR(10)||'b', 'a[[:space:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
a
b
SELECT REGEXP_SUBSTR( 'a'||CHR(11)||'b', 'a[[:space:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
a
b
SELECT REGEXP_SUBSTR( 'a'||CHR(12)||'b', 'a[[:space:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
a
b
SELECT REGEXP_SUBSTR( 'a'||CHR(13)||'b', 'a[[:space:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
b
SELECT REGEXP_SUBSTR( 'a'||CHR(32)||'b', 'a[[:space:]]b' ) AS RESULT FROM DUAL;
RESULT
--------------------
a b
SQL> SELECT REGEXP_SUBSTR( 'aBcD1234', '[[:upper:]]+', 1, 1 ) AS RESULT FROM DUAL;
RESULT
--------------------
B
SQL> SELECT REGEXP_SUBSTR( 'aBcD1234', '[[:upper:]]+', 1, 2 ) AS RESULT FROM DUAL;
RESULT
--------------------
D
SQL> SELECT REGEXP_SUBSTR( 'AEd019', '[[:xdigit:]]+' ) AS RESULT FROM DUAL;
RESULT
--------------------
AEd019
SQL> SELECT REGEXP_SUBSTR( 'AEGd019', '[[:xdigit:]]+', 1, 1 ) AS RESULT FROM DUAL;
RESULT
--------------------
AE
SQL> SELECT REGEXP_SUBSTR( 'AEGd019', '[[:xdigit:]]+', 1, 2 ) AS RESULT FROM DUAL;
RESULT
--------------------
d019
SQL> SELECT REGEXP_SUBSTR( 'abc', '[[:word:]]+' ) AS RESULT FROM DUAL;
SELECT REGEXP_SUBSTR( 'abc', '[[:word:]]+' ) AS RESULT FROM DUAL
*
ERROR at line 1:
ORA-12729: invalid character class in regular expression
postgres=# SELECT REGEXP_MATCHES( 'abc', '[[:word:]]+' ) AS RESULT;
ERROR: invalid regular expression: invalid character class
mysql> SELECT REGEXP_SUBSTR( 'abCD987_', '[[:word:]]+' ) AS RESULT;
+----------+
| RESULT |
+----------+
| abCD987_ |
+----------+
1 row in set (0.02 sec)
※ [:word:] character class는 POSIX 표준이 아닌 Perl 호환 정규 표현식이며 Oracle, PostgreSQL에서 지원하지 않는다.
- 고려사항
- Bracket 짝이 맞지 않을 때
< Oracle >
SQL> SELECT REGEXP_SUBSTR( '[digit]', '[:digit:]]' ) AS RESULT FROM DUAL;
RESULT
--------------------
t]
SQL> SELECT REGEXP_SUBSTR( '[digit]', '[[:digit:]' ) AS RESULT FROM DUAL;
SELECT REGEXP_SUBSTR( '[digit]', '[[:digit:]' ) AS RESULT FROM DUAL
*
ERROR at line 1:
ORA-12726: unmatched bracket in regular expression
< PostgreSQL >
postgres=# SELECT REGEXP_MATCHES( '[digit]', '[:digit:]]' ) AS RESULT;
result
--------
{t]}
(1개 행)
postgres=# SELECT REGEXP_MATCHES( '[digit]', '[[:digit:]' ) AS RESULT;
ERROR: invalid regular expression: brackets [] not balanced
< MySQL >
mysql> SELECT REGEXP_SUBSTR( '[digit]', '[:digit:]]' ) AS RESULT;
+--------+
| RESULT |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> SELECT REGEXP_SUBSTR( '[digit1]', '[:digit:]]' ) AS RESULT;
+--------+
| RESULT |
+--------+
| 1] |
+--------+
1 row in set (0.00 sec)
mysql> SELECT REGEXP_SUBSTR( '[digit]', '[[:digit:]' ) AS RESULT;
ERROR 3696 (HY000): The regular expression contains an unclosed bracket expression.
< MariaDB >
MariaDB [test]> SELECT REGEXP_SUBSTR( '[digit]', '[:digit:]]' ) AS RESULT;
ERROR 1139 (42000): Got error 'POSIX named classes are supported only within a class at offset ' from regexp
MariaDB [test]> SELECT REGEXP_SUBSTR( '[digit1]', '[:digit:]]' ) AS RESULT;
ERROR 1139 (42000): Got error 'POSIX named classes are supported only within a class at offset ' from regexp
※ MySQL, MariaDB는 처리가 좀 다르지만, 세 벤더 모두 닫히지 않은 bracket에 대한 에러 처리는 당연히 동일하다.
- caret의 위치
- [^[:class_name:]] [[^:class_name:]] [[:^class_name:]] 비교
- Oracle
# Not digit #
SQL> SELECT REGEXP_SUBSTR( '1d]', '[^[:digit:]]' ) AS RESULT FROM DUAL;
RESULT
--------------------
d
# specific character & string ']' #
SQL> SELECT REGEXP_SUBSTR( '1d]', '[[^:digit:]]' ) AS RESULT FROM DUAL;
RESULT
--------------------
d]
SQL> SELECT REGEXP_SUBSTR( '1d[]', '[[^:digit:]]' ) AS RESULT FROM DUAL;
RESULT
------------------------------
[]
SQL> SELECT REGEXP_SUBSTR( '1d]', '[[:^digit:]]' ) AS RESULT FROM DUAL;
SELECT REGEXP_SUBSTR( '1d]', '[[:^digit:]]' ) AS RESULT FROM DUAL
*
ERROR at line 1:
ORA-12729: invalid character class in regular expression
- PostgreSQL
# Not digit #
postgres=# SELECT REGEXP_MATCHES( '1d]', '[^[:digit:]]', 'g' ) AS RESULT;
result
--------
{d}
{]}
(2개 행)
# specific character & string ']' #
postgres=# SELECT REGEXP_MATCHES( '1d]', '[[^:digit:]]', 'g' ) AS RESULT;
result
--------
{d]}
(1개 행)
postgres=# SELECT REGEXP_MATCHES( '1d[]', '[[^:digit:]]', 'g' ) AS RESULT;
result
--------
{[]}
(1개 행)
postgres=# SELECT REGEXP_MATCHES( '1d]', '[[:^digit:]]', 'g' ) AS RESULT;
ERROR: invalid regular expression: invalid character class
- MySQL
# Not digit #
mysql> SELECT REGEXP_SUBSTR( '1d]', '[^[:digit:]]' ) AS RESULT;
+--------+
| RESULT |
+--------+
| d |
+--------+
1 row in set (0.00 sec)
# negative specific character same as [^:digit:] #
mysql> SELECT REGEXP_SUBSTR( '1d]', '[[^:digit:]]' ) AS RESULT;
+--------+
| RESULT |
+--------+
| 1 |
+--------+
1 row in set (0.01 sec)
mysql> SELECT REGEXP_SUBSTR( '1d]', '[[^:digit:]]', 1, 2 ) AS RESULT;
+--------+
| RESULT |
+--------+
| ] |
+--------+
1 row in set (0.01 sec)
mysql> SELECT REGEXP_SUBSTR( ':digit:1d]', '[[^:digit:]]' ) AS RESULT;
+--------+
| RESULT |
+--------+
| 1 |
+--------+
1 row in set (0.01 sec)
# Not digit #
mysql> SELECT REGEXP_SUBSTR( '1d]', '[[:^digit:]]' ) AS RESULT;
+--------+
| RESULT |
+--------+
| d |
+--------+
1 row in set (0.03 sec)
- MariaDB
# Not digit #
MariaDB [test]> SELECT REGEXP_SUBSTR( '1d]', '[^[:digit:]]' ) AS RESULT;
+--------+
| RESULT |
+--------+
| d |
+--------+
1 row in set (0.001 sec)
# specific character & string ']' #
MariaDB [test]> SELECT REGEXP_SUBSTR( '1d]', '[[^:digit:]]' ) AS RESULT;
+--------+
| RESULT |
+--------+
| d] |
+--------+
1 row in set (0.000 sec)
MariaDB [test]> SELECT REGEXP_SUBSTR( '1d[]', '[[^:digit:]]' ) AS RESULT;
+--------+
| RESULT |
+--------+
| [] |
+--------+
1 row in set (0.001 sec)
# Not digit #
MariaDB [test]> SELECT REGEXP_SUBSTR( '1d]', '[[:^digit:]]' ) AS RESULT;
+--------+
| RESULT |
+--------+
| d |
+--------+
1 row in set (0.000 sec)
