[REGEX] :: POSIX ERE Meta Character '' 11. | ( vertical bar ) ''

IT/Regular Expression 2019. 10. 17. 16:34

POSIX ERE( Extended Regular Expression )

  1. +
  2. ?
  3. |



11. | ( Vertical Bar )

구문 ( Syntax )

<vertical_bar> ::= |
  • 사용 예
    <vertical_bar>

<pattern_expr><vertical_bar>

<vertical_bar><pattern_expr>

<pattern_expr><vertical_bar><pattern_expr>

설명 ( Description )

  • '또는', 'or'를 의미한다.
  • 일반적으로, 좌측 <pattern_expr>부터 검사한 뒤, 매칭이 없으면 우측 <pattern_expr>을 검사한다.
    • ex) REGEXP_SUBSTR( 'take', '(m|t)ake' ) => take
    • ex) REGEXP_SUBSTR( 'aaa', 'aa|aaa' ) => aa
    • 예외) PostgreSQL : 예제 확인
  • <patten_expr> 중 하나가 empty string일 경우, 제쳐두고 나머지 <pattern_expr>을 우선적으로 매칭한다.
    • ex) REGEXP_SUBSTR( 'gdb', '|g' ) => 'g'
      • 이 때 그 나머지 <pattern_expr>의 매칭이 없을 경우 zero-length match가 발생한다.
      • ex) REGEXP_SUBSTR( 'abc', '|g' ) => zero-length match
  • 두 <pattern_expr> 모두 empty string일 경우 zero-length match가 발생한다.
    • ex) |
  • <pattern_expr>이 string일 경우, 그 string을 다룬다.
    • ex) trick|treat => trick or treat ( but not trickreat or trictreat )


예제 ( Examples )

SQL> SELECT REGEXP_SUBSTR( 'abc', 'a|b' ) AS RESULT FROM DUAL;

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

SQL> SELECT REGEXP_SUBSTR( 'abc', 'b|a' ) AS RESULT FROM DUAL;

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

SQL> SELECT REGEXP_SUBSTR( 'bbb', 'bb|bbb' ) AS RESULT FROM DUAL;

RESULT
--------------------
bb

SQL> SELECT REGEXP_SUBSTR( 'bbb', 'bbb|bb' ) AS RESULT FROM DUAL;

RESULT
--------------------
bbb


SQL> SELECT REGEXP_SUBSTR( 'ababcdcdcd', '(ab){2}|(cd)+' ) AS RESULT FROM DUAL;

RESULT
------------------------------
abab

SQL> SELECT REGEXP_SUBSTR( 'ababcdcdcd', '(ab){2}|(cd)+', 1, 2 ) AS RESULT FROM DUAL;

RESULT
------------------------------
cdcdcd

SQL> SELECT I1, REGEXP_SUBSTR( I1, '(1[0-2]|0?[1-9])/(3[01]|[12][0-9]|0?[1-9])(st|nd|rd|th)?' ) AS RESULT FROM T1;

I1                             RESULT
------------------------------ ------------------------------
12/25                          12/25
02/14                          02/14
11/26th                        11/26th
4/3rd                          4/3rd
10/01st                        10/01st


SQL> SELECT REGEXP_SUBSTR( 'mississippi', 'mi(s|ss)i(s|ss)i(p|pp)i' ) AS RESULT FROM DUAL;

RESULT
--------------------
mississippi

SQL> SELECT REGEXP_SUBSTR( '801010-1234567',
 '[6-9][0-9]([0][1-9]|[1][0-9])([0][1-9]|[1-2][0-9]|[3][0-1])-[1-4][0-9]{6}' ) AS RESULT FROM DUAL;

RESULT
--------------------
801010-1234567

SQL> SELECT REGEXP_SUBSTR( '800010-1234567',
 '[6-9][0-9]([0][1-9]|[1][0-9])([0][1-9]|[1-2][0-9]|[3][0-1])-[1-4][0-9]{6}' ) AS RESULT FROM DUAL;

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

SQL> SELECT REGEXP_SUBSTR( '801010-2234567',
 '[6-9][0-9]([0][1-9]|[1][0-9])([0][1-9]|[1-2][0-9]|[3][0-1])-[1-4][0-9]{6}' ) AS RESULT FROM DUAL;

RESULT
--------------------
801010-2234567

SQL> SELECT REGEXP_SUBSTR( '801010-5234567',
 '[6-9][0-9]([0][1-9]|[1][0-9])([0][1-9]|[1-2][0-9]|[3][0-1])-[1-4][0-9]{6}' ) AS RESULT FROM DUAL;

RESULT
--------------------
NULL
  • zero-length match : PostgreSQL로 테스트
    postgres=# SELECT REGEXP_MATCHES( 'abc', 'a|', 'g' ) AS RESULT;
 result 
--------
 {a}
 {""}
 {""}
 {""}
(4개 행)

postgres=# SELECT REGEXP_MATCHES( 'abc', '|a', 'g' ) AS RESULT;
 result 
--------
 {a}
 {""}
 {""}
 {""}
(4개 행)

postgres=# SELECT REGEXP_MATCHES( 'abc', 'z|', 'g' ) AS RESULT;
 result 
--------
 {""}
 {""}
 {""}
 {""}
(4개 행)

postgres=# SELECT REGEXP_MATCHES( 'abc', '|z', 'g' ) AS RESULT;
 result 
--------
 {""}
 {""}
 {""}
 {""}
(4개 행)

postgres=# SELECT REGEXP_MATCHES( 'abc', '|', 'g' ) AS RESULT;
 result 
--------
 {""}
 {""}
 {""}
 {""}
(4개 행)


저작자표시 비영리

'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 '' 8. [ ] ( bracket ) ''  (0) 2019.08.28
[REGEX] :: POSIX BRE Meta Character '' 7. { } ( brace ) ''  (0) 2019.08.28
[REGEX] :: POSIX BRE Meta Character '' 6. ( ) ( parenthesis ) ''  (0) 2019.07.16
블로그 이미지

차트

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

,

[REGEX] :: POSIX ERE Meta Character '' 10. ? ( question mark ) ''

IT/Regular Expression 2019. 10. 17. 16:31

POSIX ERE( Extended Regular Expression )

  1. +
  2. ?
  3. |



10. ? ( Question Mark )

구문 ( Syntax )

<question_mark> ::= ?
  • 사용 예
    <pattern_expr><question_mark>

설명 ( Description )

  • 0 or 1
  • 수량자(quantifier)로, <question_mark> 앞의 <pattern_expr>이 없거나 하나 있음을 의미한다.
    • ex) a? => 　, a
  • <question_mark>만 있는 경우
    • ex) ?
    • 결과
      • Oracle : zero-length match
      • PostgreSQL, MySQL, MariaDB : error 처리
  • zero-length match가 발생할 수 있다.


예제 ( Examples )

SQL> SELECT REGEXP_SUBSTR( 'god', 'goo?d' ) AS RESULT FROM DUAL;

RESULT
--------------------
god

SQL> SELECT REGEXP_SUBSTR( 'good', 'goo?d' ) AS RESULT FROM DUAL;

RESULT
--------------------
good

SQL> SELECT REGEXP_SUBSTR( 'goood', 'goo?d' ) AS RESULT FROM DUAL;

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

SQL> SELECT REGEXP_SUBSTR( 'ababc', '(ab)?' ) AS RESULT FROM DUAL;

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

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

RESULT
--------------------
knight

SQL> SELECT REGEXP_SUBSTR( 'abc', 'z?' ) AS RESULT FROM DUAL;

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

SQL> SELECT REGEXP_COUNT( 'abc', 'z?' ) AS RESULT FROM DUAL;

    RESULT
----------
         4
  • <question_mark>만 있는 경우
    < 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


저작자표시 비영리

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

[REGEX] :: POSIX ERE Meta Character '' 11. | ( vertical bar ) ''  (0) 2019.10.17
[REGEX] :: POSIX ERE Meta Character '' 9. + ( plus ) ''  (0) 2019.10.17
[REGEX] :: POSIX BRE Meta Character '' 8. [ ] ( bracket ) ''  (0) 2019.08.28
[REGEX] :: POSIX BRE Meta Character '' 7. { } ( brace ) ''  (0) 2019.08.28
[REGEX] :: POSIX BRE Meta Character '' 6. ( ) ( parenthesis ) ''  (0) 2019.07.16
블로그 이미지

차트

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

,

[REGEX] :: POSIX ERE Meta Character '' 9. + ( plus ) ''

IT/Regular Expression 2019. 10. 17. 16:28

POSIX ERE( Extended Regular Expression )

  1. +
  2. ?
  3. |


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



저작자표시 비영리

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

[REGEX] :: POSIX ERE Meta Character '' 11. | ( vertical bar ) ''  (0) 2019.10.17
[REGEX] :: POSIX ERE Meta Character '' 10. ? ( question mark ) ''  (0) 2019.10.17
[REGEX] :: POSIX BRE Meta Character '' 8. [ ] ( bracket ) ''  (0) 2019.08.28
[REGEX] :: POSIX BRE Meta Character '' 7. { } ( brace ) ''  (0) 2019.08.28
[REGEX] :: POSIX BRE Meta Character '' 6. ( ) ( parenthesis ) ''  (0) 2019.07.16
블로그 이미지

차트

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

,

글 보관함

달력

«   2019/10   »
일 월 화 수 목 금 토
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 29 30 31

티스토리툴바