[REGEX] :: POSIX BRE Meta Character '' 8. [ ] ( bracket ) ''

IT/Regular Expression 2019. 8. 28. 10:38

POSIX BRE( Basic Regular Expression )

  1. ^
  2. $
  3. .
  4. *
  5. \
  6. (
  7. {
  8. [



8. [ ] ( Bracket, 대괄호 )

구문 ( Syntax )

<left_bracket> ::= [

<right_bracket> ::= ]
  • 사용 예
    <left_bracket><positive_bracket_expr><right_bracket>

<left_bracket><negative_bracket_expr><right_bracket>

<positive_bracket_expr> ::=
  <bracket_expr>

<negative_bracket> ::=
  <caret><bracket_expr>

<caret> ::= ^

    <bracket_expr> ::=
  <specific_character>
| <range_character>
| <collation_class>
| <character_class>
| <equivalence_class>

    <specific_character> ::=
  <pattern_expr>


ex) [abc]

    <range_character> ::=
  <pattern_expr><hyphen><pattern_expr>

<hyphen> ::= -


ex) [0-9]

    <collation_class> ::=
  <left_bracket><period><pattern_expr><period><right_bracket>

<period> ::= .


ex) [[.Ch.]]

    <character_class> ::=
  <left_bracket><colon>alnum<colon><right_bracket>
| <left_bracket><colon>alpha<colon><right_bracket>
| <left_bracket><colon>blank<colon><right_bracket>
| <left_bracket><colon>cntrl<colon><right_bracket>
| <left_bracket><colon>digit<colon><right_bracket>
| <left_bracket><colon>graph<colon><right_bracket>
| <left_bracket><colon>lower<colon><right_bracket>
| <left_bracket><colon>print<colon><right_bracket>
| <left_bracket><colon>punct<colon><right_bracket>
| <left_bracket><colon>space<colon><right_bracket>
| <left_bracket><colon>upper<colon><right_bracket>
| <left_bracket><colon>word<colon><right_bracket>
| <left_bracket><colon>xdigit<colon><right_bracket>

<colon> ::= :


ex) [[:alnum:]],  [[:xdigit:]]

    <equivalence_class> ::=
  <left_bracket><equal><pattern_expr><equal><right_bracket>

<equal> ::= =


ex) [[=e=]]

설명 ( Description )

  • 한 문자를 의미하며 사용 방법에 따라 의미가 다양하게 바뀐다.
  • <left_bracket> 이후 다음 문자들을 제외한 나머지 문자들은 모두 일반 문자로 취급한다.
    • ^ ( caret ) : negative
      • ex) [^a]
    • - ( hyphen ) : range
      • ex) [A-Z]
    • [ ( open bracket ) : 각종 클래스
      • 클래스 안에서 : ( colon ), .( period ), = ( equal )은 각 클래스의 구문으로 쓰인다.
        • ex) [[:cntrl:]]
        • ex) [[.Ch.]]
        • ex) [[=n=]]
    • ] ( close bracket ) : bracket 구문 종료

예를 들어 [*]는 단지 문자 '*'를 의미한다.

  • 종류는 다음과 같다.
    • <specific_character>
    • <range_character>
    • <collation_class>
    • <character_class>
    • <equivalence_class>
<specific_character>
  • <pattern_expr>에 명시된 문자들 중 한 문자를 의미한다.
  • 예시
    [a] : 알파벳 'a'를 의미
[^a] : 알파벳 'a'가 아닌 다른 한 문자를 의미
[abc] : 'a' 혹은 'b' 혹은 'c' 중 한 문자를 의미
[^abc] : 'a', 'b', 'c'를 제외한 한 문자를 의미
<range_character>
  • <hyphen>을 사용하며 명시된 <character_1>과 <character_2> 범위 내의 한 문자를 의미한다.
  • 범위의 순서 기준은 ASCII 코드이다.
  • <character_1>의 ASCII 코드 숫자가 <character_2>의 ASCII 코드 숫자보다 크면 error 처리한다.
  • 한글도 가능하며, 한글 순서의 기준은 추후에 다룬다.
  • 예시
    [a-z] : 알파벳 소문자 한 문자를 의미 ( ASCII code 97 ~ 122 )
[0-9] : 10진 수 0~9중 한 문자를 의미 ( ASCII code 48 ~ 57 )
[A-Za-z] : 알파벳 대문자 및 소문자 한 문자를 의미
[a-a] : 알파벳 a를 의미
[b-a] : error
<collation_class>
  • multibyte 문자가 한 문자를 의미할 때, 이 collation class를 사용하여 나타낸다.
  • NLS_LANGUAGE와 NLS_SORT를 사용해야하며, 해당 국가의 문자가 알파벳으로 표현하였을 때 두 개 이상일 때
    그 문자들을 한 문자로 처리한다.
  • 국가별로 해당 예약어가 정해져 있다.
  • 예시
    ALTER SESSION SET NLS_LANGUAGE=SPANISH;
ALTER SESSION SET NLS_SORT=XSPANISH;

source string : El caballo, Chico come la tortilla
pattern : [[.ch.]]

matched result : Ch
<character_class>
  • 각 문자 클래스가 의미하는 문자들 중 한 문자를 의미한다.
  • 문자 클래스의 종류는 다음과 같다.
클래스 이름설명ASCII CODE range비고
[:alnum:]알파벳과 숫자(48 ~ 57), (65 ~ 90), (97 ~ 122) 
총 62개
[:alpha:]알파벳(65 ~ 90), (97 ~ 122)
총 52개
[:blank:]공백( 스페이스 )(32)
총 1개
[:cntrl:]제어 문자( ^[, ^E 등 )(0 ~ 31), (127)
총 33개
[:digit:]10진수(48 ~ 57)
총 10개
[:graph:]visible한 모든 문자( 공백 제외 )(33 ~ 126)
총 94개
[:lower:]알파벳 소문자(97 ~ 122)
총 26개
[:print:]visible한 모든 문자( 공백 포함 )(32 ~ 126)
총 95개
[:punct:]구두점 문자(33~47), (58~64), (91~96), (123~126)
총 32개
[:space:]모든 공백 문자( [:blank:], newline, carriage return, ... )(9 ~ 13), (32)
총 6개
[:upper:]알파벳 대문자(65 ~ 90)
총 26개
[:word:]알파벳과 숫자,언더바(48 ~ 57), (65 ~ 90), (95), (97 ~ 122) 
총 63개		POSIX 표준이 아님
[:xdigit:]16진수, [A-Fa-f0-9]와 동일함(48 ~ 57), (65 ~ 70), (97 ~ 102)
총 22개
<equivalence_class>
  • equivalence class 는 해당 <pattern_expr>과 동치인 의미를 가진 다른 multibyte 문자들 중 한 문자를 의미한다.
  • 이는 해당 database의 지원 locale 룰에 따라 범주가 달라진다.
  • 예시
    [=n=] : n, N, ñ, El Niño, ...

예제 ( Examples )

  • specific character
    SQL> SELECT REGEXP_SUBSTR( 'abc', '[a]' ) AS RESULT FROM DUAL;

RESULT
--------------------
a

SQL> SELECT REGEXP_SUBSTR( 'abc', '[ab]', 1, 1 ) AS RESULT FROM DUAL;

RESULT
--------------------
a

SQL> SELECT REGEXP_SUBSTR( 'abc', '[ab]', 1, 2 ) AS RESULT FROM DUAL;

RESULT
--------------------
b

SQL> SELECT REGEXP_SUBSTR( 'abc', '[^ab]' ) AS RESULT FROM DUAL;

RESULT
--------------------
c

SQL> SELECT REGEXP_SUBSTR( 'abbccc', '[ab^]+' ) AS RESULT FROM DUAL;

RESULT
--------------------
abb

SQL> SELECT REGEXP_SUBSTR( 'ab bccc', '[ab^]+', 1, 1 ) AS RESULT FROM DUAL;

RESULT
--------------------
ab

SQL> SELECT REGEXP_SUBSTR( 'ab bccc', '[ab^]+', 1, 2 ) AS RESULT FROM DUAL;

RESULT
--------------------
b

SQL> SELECT REGEXP_SUBSTR( '^', '[a^]' ) AS RESULT FROM DUAL;

RESULT
------------------------------
^

SQL> SELECT REGEXP_SUBSTR( 'b^', '[a^b]' ) AS RESULT FROM DUAL;

RESULT
------------------------------
b

SQL> SELECT REGEXP_SUBSTR( 'b^', '[^b]' ) AS RESULT FROM DUAL;

RESULT
------------------------------
^

SQL> SELECT REGEXP_SUBSTR( 'b^', '[ ^b]' ) AS RESULT FROM DUAL;

RESULT
------------------------------
b

SQL> SELECT REGEXP_SUBSTR( 'b^', '[ ^b]', 1, 2 ) AS RESULT FROM DUAL;

RESULT
------------------------------
^

SQL> SELECT REGEXP_SUBSTR( 'abbccc', '[^^ab]+' ) AS RESULT FROM DUAL;

RESULT
--------------------
ccc

※ 맨 앞의 ^이 아니면, 단지 스트링 ^취급한다.
  • range character
    SQL> SELECT REGEXP_SUBSTR( 'abCD12<>?', '[a-z]' ) AS RESULT FROM DUAL;

RESULT
--------------------
a

SQL> SELECT REGEXP_SUBSTR( 'abCD12<>?', '[A-Z]' ) AS RESULT FROM DUAL;

RESULT
--------------------
C

SQL> SELECT REGEXP_SUBSTR( 'abCD12<>?', '[^a-zA-Z]' ) AS RESULT FROM DUAL;

RESULT
--------------------
1

SQL> SELECT REGEXP_SUBSTR( 'abCD12<>?', '[a--z]' ) AS RESULT FROM DUAL;
SELECT REGEXP_SUBSTR( 'abCD12<>?', '[a--z]' ) AS RESULT FROM DUAL
                                   *
ERROR at line 1:
ORA-12728: invalid range in regular expression

SQL> SELECT REGEXP_SUBSTR( 'abCD12<>?', '[0-9]' ) AS RESULT FROM DUAL;

RESULT
--------------------
1

SQL> SELECT REGEXP_SUBSTR( 'abCD12<>?', '[^0-9]' ) AS RESULT FROM DUAL;

RESULT
--------------------
a

SQL> SELECT REGEXP_SUBSTR( 'abCD12<>?', '[:-@]' ) AS RESULT FROM DUAL;

RESULT
--------------------
<
  • collation class
    < Oracle >
SQL> ALTER SESSION SET NLS_LANGUAGE=SPANISH;

Sesión modificada.

SELECT REGEXP_SUBSTR(
   'El caballo, Chico come la tortilla.',
   '[[:alpha:]]*[ch][[:alpha:]]*',1,1,'i') AS RESULT
    FROM dual;

RESULT
---------------------
caballo

SQL> ALTER SESSION SET NLS_SORT=XSPANISH;

Sesión modificada.

SELECT REGEXP_SUBSTR(
   'El caballo, Chico come la tortilla.',
   '[[:alpha:]]*[[.ch.]][[:alpha:]]*',1,1,'i') AS RESULT
    FROM dual;

RESULT
---------------
Chico

< PostgreSQL >
Note: PostgreSQL currently does not support multi-character collating elements.

< MySQL >
The Spencer library supports collating element bracket expressions ([.characters.] notation). 
ICU( International Components for Unicode ) does not.
  • 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)
 
caret의 위치에 따른 문자 클래스의 벤더별 처리
　DBMS　　[^[:class_name:]]　　[[^:class_name:]]　　[[:^class_name:]]　
　Oracle　　negative class　　normal bracket　　error　
　PostgreSQL　　negative class　　normal bracket　　error　
　MySQL　　negative class　　same as [^:class_name:]　　negative class　
　MariaDB　　negative class　　normal bracket　　negative class　
  • equivalence class
    SQL> SELECT REGEXP_SUBSTR('eéëèÉËÈE', '[[=e=]]+') AS RESULT FROM DUAL;

RESULT
------------------------------------------
eéëèÉËÈE

SQL> SELECT REGEXP_SUBSTR('eéëèÉËÈE', '[[=E=]]+') AS RESULT FROM DUAL;

RESULT
------------------------------------------
eéëèÉËÈE

SQL> SELECT REGEXP_SUBSTR('eéëèÉËÈE', '[[=É=]]+') AS RESULT FROM DUAL;

RESULT
------------------------------------------
eéëèÉËÈE

※ PostgreSQL, MySQL, MariaDB에서는 지원하지 않음.


저작자표시 비영리

'IT > Regular Expression' 카테고리의 다른 글

[REGEX] :: POSIX ERE Meta Character '' 10. ? ( question mark ) ''  (0) 2019.10.17
[REGEX] :: POSIX ERE Meta Character '' 9. + ( plus ) ''  (0) 2019.10.17
[REGEX] :: POSIX BRE Meta Character '' 7. { } ( brace ) ''  (0) 2019.08.28
[REGEX] :: POSIX BRE Meta Character '' 6. ( ) ( parenthesis ) ''  (0) 2019.07.16
[REGEX] :: POSIX BRE Meta Character '' 5. \ ( back slash ) ''  (0) 2019.07.16
블로그 이미지

차트

소소한 일상 C코드 DB 항상 행복하게^-^★

,

글 보관함

달력

«   2025/02   »
일 월 화 수 목 금 토
1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28

티스토리툴바