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에서는 지원하지 않음.


블로그 이미지

차트

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

,