POSIX ERE( Extended Regular Expression )

9. +
10. ?
11. |

9. + ( Plus )

구문 ( Syntax )

<plus> ::= +

• 사용 예

  <pattern_expr><plus>
  

설명 ( Description )

• 1 or MORE
• 수량자(quantifier)로, <plus> 앞의 <pattern_expr>이 하나 이상 있음을 의미한다.
  • ex) a+ => a, aa, aaa, ...
• <plus>만 있는 경우
  • ex) +
  • 결과
    • Oracle : zero-length match
    • PostgreSQL, MySQL, MariaDB : error 처리

• 다른 수량자들과는 달리 zero-length match가 발생하지 않는다.
  • Oracle은 예외적으로 empty string에 대한 +도 zero-length match가 발생한다.
    • ex) +

예제 ( Examples )

SQL> SELECT REGEXP_SUBSTR( 'aaaaabbb', 'a+' ) AS RESULT FROM DUAL;

RESULT
--------------------
aaaaa

SQL> SELECT REGEXP_SUBSTR( 'abababc', '(ab)+' ) AS RESULT FROM DUAL;

RESULT
--------------------
ababab

SQL> SELECT REGEXP_SUBSTR( 'knight', '.+knight' ) AS RESULT FROM DUAL;

RESULT
--------------------
NULL

SQL> SELECT REGEXP_SUBSTR( 'GOLDILOCKS', 'GOLD(.+)' ) AS RESULT FROM DUAL;

RESULT
--------------------
GOLDILOCKS

• <plus>만 있는 경우

  < Oracle >
  SQL> SELECT REGEXP_SUBSTR( 'ab', '+' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_COUNT( 'ab', '+' ) AS RESULT FROM DUAL;
  
      RESULT
  ----------
           3
  
  
  < PostgreSQL >
  postgres=# SELECT REGEXP_MATCHES( 'ab', '+', 'g' ) AS RESULT;
  ERROR:  invalid regular expression: quantifier operand invalid
  
  
  < MySQL >
  mysql> SELECT REGEXP_SUBSTR( 'ab', '+' ) AS RESULT;
  ERROR 3688 (HY000): Syntax error in regular expression on line 1, character 1.
  
  
  < MariaDB >
  MariaDB [test]> SELECT REGEXP_SUBSTR( 'ab', '+' ) AS RESULT;
  ERROR 1139 (42000): Got error 'nothing to repeat at offset 0' from regexp
  
  

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>

• 각 문자 클래스가 의미하는 문자들 중 한 문자를 의미한다.
• 문자 클래스의 종류는 다음과 같다.

<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의 위치에 따른 문자 클래스의 벤더별 처리

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

POSIX BRE( Basic Regular Expression )

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

7. { } ( Brace, 중괄호 )

구문 ( Syntax )

<left_brace> ::= {

<right_brace> ::= }

• 사용 예

  <pattern_expr><left_brace><brace_expr><right_brace>
  
  <brace_expr> ::=
    <integer_1>
  | <integer_1><comma>
  | <integer_1><comma><integer_2>
  
  <comma> ::= ,
  
  

설명 ( Description )

• 수량자(quantifier)로, <left_brace> 앞에 있는 <pattern_expr>의 '개수, 반복 횟수'를 의미한다.

• <brace_expr>에 하나의 <integer_1> 만 명시한 경우
  • 정확히 <integer>개를 의미한다.
    • ex) a{2} => aa
    • ex) (ab){3} => ababab

• <brace_expr>에 <integer_1>와 <comma>를 명시한 경우
  • <integer>개 이상을 의미한다.
    • ex) a{3,} => aaa, aaaa, aaaaa, ...

• <brace_expr>에 <integer_1>, <comma>, <integer_2>를 명시한 경우
  • <integer_1>개 이상 <integer_2>개 이하를 의미한다.
    • ex) go{1, 3} => go, goo, gooo
  • <integer_1> ≤ <integer_2> 를 만족해야 한다. ( <integer_1> ＞ <integer_2> 인 경우 error 처리 )
    • ex) (ab){5,2} => error 처리

• 지정된 사용법이 아닌 경우
  • <integer_1>에 음수 및 소수가 있는 경우
    • ex) a{-1}
    • ex) z{1.5}
  • <comma>가 아닌 다른 문자가 있는 경우( 숫자 아닌 문자 )
    • ex) g{1~10}
  • <comma> 뒤에 <integer_2>가 아닌 다른 문자가 있는 경우
    • ex) (ab){2,s}
  • 맨 뒤에 <right_brace>가 없는 경우
    • ex) c{3,5
  • 결과
    • Oracle, MariaDB는 전체를 str (일반 문자)로 처리한다.
    • PostreSQL, MySQL은 error 처리한다.

• <integer_1> 이 0인 경우, zero-length match가 발생할 수 있다.

  • ex) c{0}
  • ex) z{0,}
  • ex) (ab){0, 5}

• <brace_expr>에 아무것도 명시하지 않은 경우
  • ex) a{}
  • 결과
    • Oracle, PostgreSQL, MariaDB : string 처리
    • MySQL : error 처리

• <pattern_expr>이 <left_brace> 앞에 명시되어있지 않은 경우
  • ex) {3}
  • 결과
    • Oracle : zero-length match
    • PostgreSQL, MySQL, MariaDB : error 처리

예제 ( Examples )

SQL> SELECT REGEXP_SUBSTR( 'aaaab', 'a{2}' ) AS RESULT FROM DUAL;

RESULT
--------------------
aa

SQL> SELECT REGEXP_SUBSTR( 'aaaab', 'a{1,}' ) AS RESULT FROM DUAL;

RESULT
--------------------
aaaa

SQL> SELECT REGEXP_SUBSTR( 'aaaab', 'a{1,3}' ) AS RESULT FROM DUAL;

RESULT
--------------------
aaa

• 지정된 사용법이 아닌 경우

  SQL> SELECT REGEXP_SUBSTR( 'aaa', 'a{-1}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_SUBSTR( 'a{-1}', 'a{-1}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  a{-1}
  
  SQL> SELECT REGEXP_SUBSTR( 'aaa', 'a{1~3}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_SUBSTR( 'a{1~3}', 'a{1~3}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  a{1~3}
  
  SQL> SELECT REGEXP_SUBSTR( 'aaa', 'a{1.5}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_SUBSTR( 'a{1.5}', 'a{1.5}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  a{1.5}
  
  SQL> SELECT REGEXP_SUBSTR( 'zzz', 'z{1,a}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_SUBSTR( 'z{1,a}', 'z{1,a}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  z{1,a}
  
  SQL> SELECT REGEXP_SUBSTR( 'zzz', 'z{1, 2}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_SUBSTR( 'z{1, 2}', 'z{1, 2}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  z{1, 2}
  
  SQL> SELECT REGEXP_SUBSTR( 'aaa', 'a{1' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_SUBSTR( 'a{1', 'a{1' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  a{1
  
  SQL> SELECT REGEXP_SUBSTR( 'zzz', 'z{1,' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_SUBSTR( 'z{1,', 'z{1,' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  z{1,
  
  SQL> SELECT REGEXP_SUBSTR( 'zzz', 'z{1,3' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_SUBSTR( 'z{1,3', 'z{1,3' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  z{1,3
  
  
  < MariaDB >
  MariaDB [test]> SELECT REGEXP_SUBSTR( 'a{1~3}', 'a{1~3}' ) AS RESULT FROM DUAL;
  +--------+
  | RESULT |
  +--------+
  | a{1~3} |
  +--------+
  1 row in set (0.001 sec)
  
  
  < PostgreSQL >
  postgres=# SELECT REGEXP_MATCHES( 'a{1,3', 'a{1~3}' ) AS RESULT;
  ERROR:  invalid regular expression: invalid repetition count(s)
  
  
  < MySQL >
  mysql> SELECT REGEXP_SUBSTR( 'aaa', 'a{1~3}' ) AS RESULT FROM DUAL;
  ERROR 3692 (HY000): Incorrect description of a {min,max} interval.
  
  
  ※ Oracle, MariaDB는 지정된 사용법이 아니라면 모두 string 처리하는 반면
     PostgreSQL, MySQL은 error 처리한다.
  

• <integer>, <integer_1> 이 0인 경우

  SQL> SELECT REGEXP_SUBSTR( 'abc', 'z{0}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_COUNT( 'abc', 'z{0}' ) AS RESULT FROM DUAL;
  
      RESULT
  ----------
           4
  
  
  SQL> SELECT REGEXP_SUBSTR( 'abc', 'z{0,}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_COUNT( 'abc', 'z{0,}' ) AS RESULT FROM DUAL;
  
      RESULT
  ----------
           4
  
  
  SQL> SELECT REGEXP_SUBSTR( 'abc', 'z{0,10}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_COUNT( 'abc', 'z{0,10}' ) AS RESULT FROM DUAL;
  
      RESULT
  ----------
           4
  
  
  SQL> SELECT REGEXP_SUBSTR( 'abc', '(abc){0}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_COUNT( 'abc', '(abc){0}' ) AS RESULT FROM DUAL;
  
      RESULT
  ----------
           4
  
  
  SQL> SELECT REGEXP_SUBSTR( 'abc', '(abc){0,}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  abc
  
  SQL> SELECT REGEXP_COUNT( 'abc', '(abc){0,}' ) AS RESULT FROM DUAL;
  
      RESULT
  ----------
           2
  
  
  SQL> SELECT REGEXP_SUBSTR( 'abc', '(def){0,}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_COUNT( 'abc', '(def){0,}' ) AS RESULT FROM DUAL;
  
      RESULT
  ----------
           4
  

• <brace_expr>에 아무것도 명시하지 않은 경우

  < Oracle >
  SQL> SELECT REGEXP_SUBSTR( 'aaa', 'a{}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_SUBSTR( 'a{}', 'a{}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  a{}
  
  
  < PostgreSQL >
  postgres=# SELECT REGEXP_MATCHES( 'aaa', 'a{}', 'g' ) AS RESULT;
   result 
  --------
  (0개 행)
  
  postgres=# SELECT REGEXP_MATCHES( 'a{}', 'a{}', 'g' ) AS RESULT;
   result  
  ---------
   {"a{}"}
  (1개 행)
  
  
  < MariaDB >
  MariaDB [test]> SELECT REGEXP_SUBSTR( 'aaa', 'a{}' ) AS RESULT;
  +--------+
  | RESULT |
  +--------+
  |        |
  +--------+
  1 row in set (0.001 sec)
  
  MariaDB [test]> SELECT REGEXP_SUBSTR( 'a{}', 'a{}' ) AS RESULT;
  +--------+
  | RESULT |
  +--------+
  | a{}    |
  +--------+
  1 row in set (0.001 sec)
  
  
  < MySQL >
  mysql> SELECT REGEXP_SUBSTR( 'aaa', 'a{}' ) AS RESULT;
  ERROR 3692 (HY000): Incorrect description of a {min,max} interval.
  
  
  ※ Oracle, PostgreSQL, MariaDB는 모두 string 처리하는 반면
     MySQL은 error 처리한다.
  

• <pattern_expr>이 <left_brace> 앞에 명시되어있지 않은 경우

  < Oracle >
  SQL> SELECT REGEXP_SUBSTR( 'aaa', '{5}' ) AS RESULT FROM DUAL;
  
  RESULT
  ------------------------------
  NULL
  
  SQL> SELECT REGEXP_COUNT( 'aaa', '{5}' ) AS RESULT FROM DUAL;
  
      RESULT
  ----------
           4
  
  
  < PostgreSQL >
  postgres=# SELECT REGEXP_MATCHES( 'aaa', '{5}', 'g' ) AS RESULT;
  ERROR:  invalid regular expression: quantifier operand invalid
  
  
  < MySQL >
  mysql> SELECT REGEXP_SUBSTR( 'aaa', '{5}' ) AS RESULT;
  ERROR 3688 (HY000): Syntax error in regular expression on line 1, character 1.
  
  
  < MariaDB >
  MariaDB [test]> SELECT REGEXP_SUBSTR( 'aaa', '{5}' ) AS RESULT;
  ERROR 1139 (42000): Got error 'nothing to repeat at offset 2' from regexp
  
  

POSIX BRE( Basic Regular Expression )

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

5. \ ( Back Slash )

구문 ( Syntax )

<back_slash> ::= \

SQL> SELECT REGEXP_SUBSTR( '$999', '\$999' ) AS RESULT FROM DUAL;

RESULT
--------------------
$999

SQL> SELECT REGEXP_SUBSTR( 'Hi ^^', 'Hi \^\^' ) AS RESULT FROM DUAL;

RESULT
--------------------
Hi ^^

SQL> SELECT REGEXP_SUBSTR( 'Mr.Kim', 'Mr\.Kim' ) AS RESULT FROM DUAL;

RESULT
--------------------
Mr.Kim

SQL> SELECT REGEXP_SUBSTR( '\n means ''new line''', '\\.*' ) AS RESULT FROM DUAL;

RESULT
--------------------
\n means 'new line'

REGULAR EXPRESSION ( 정규 표현식 )

Basic Concepts

What is Regular Expression?

정규 표현식이란, 특정한 규칙을 가진 문자열의 집합을 표현하는데 사용하는 형식 언어이다.

• 즉 쉽게 말해 문자열을 다루는 하나의 언어이다.

주로 Programming Language나 Text Editor 등에서
특정 문자열의 검색, 치환 등의 처리를 위한 용도로 사용된다.

How to Express

정규 표현식은 언어마다 문법이 다르기 때문에, 표준을 먼저 정의할 필요가 있다.
크게 나누면 표준인 POSIX의 BRE와 ERE가 있고, 여기서 문법을 확장한 Perl 호환 정규 표현식으로 나뉜다.

• 표준
  • POSIX¹⁾ 기반의
    • 기본 정규 표현식( BRE : Basic Regular Expression )
    • 확장 정규 표현식( ERE : Extended Regular Expression )
• 비표준
  • Perl 호환 정규 표현식

1) POSIX란?

이식 가능 운영 체제 인터페이스( Portable Operating System Interface )의 약자로,
서로 다른 UNIX OS의 공통 API를 정리해 이식성이 높은 유닉스 응용 프로그램을 개발하기 위해
IEEE가 책정한 애플리케이션 인터페이스 규격이다.

이러한 정규 표현식에서 사용하는 기호들을 메타문자( Meta Character )라 한다.
메타문자는 표현식 내에서 특정한 의미를 갖는 문자를 일컫는다.
BRE 및 ERE의 메타문자 12개 존재한다.

• 종료 대괄호 ]나 하이픈 -은 대괄호 [ 뒤에 올 경우에만 메타문자로 인식한다.
• 종료 중괄호 }는 { 뒤에 올 경우에만 메타문자로 인식한다.

• 반면 소괄호 )는 대괄호, 중괄호와 달리 메타문자로 인식하는데,
  소괄호는 무조건 ()가 쌍으로 존재하지 않으면 에러처리하기 때문에
  메타문자로 다룬다.

• 이러한 메타문자들 앞에 백슬래시 \를 사용하면 일반 문자로 인식할 수 있다.