카테고리:

문자열 함수(정규식)

REGEXP_INSTR

문자열 주제에서 정규식 패턴의 지정된 발생 위치를 반환합니다. 일치하는 항목이 없으면 0을 반환합니다.

문자열 함수(정규식) 도 참조하십시오.

구문

REGEXP_INSTR( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <option> [ , <regexp_parameters> [ , <group_num> ] ] ] ] ] )
Copy

인자

필수:

subject

일치 항목을 검색할 문자열입니다.

pattern

일치할 패턴입니다.

선택 사항:

position

함수가 일치 항목 검색을 시작하는 문자열 시작 부분의 문자 수입니다.

기본값: 1 (일치 항목 검색은 왼쪽의 첫 번째 문자에서 시작됨)

occurrence

패턴의 어느 발생 항목을 일치할지 지정합니다. 함수는 첫 occurrence - 1 개의 일치 항목을 건너뜁니다.

기본값: 1

option

일치 항목의 첫 번째 문자의 오프셋을 반환할지(0), 아니면 일치 항목의 끝 다음에 오는 첫 번째 문자의 오프셋을 반환할지(1) 지정합니다.

기본값: 0

regexp_parameters

일치 항목을 검색하는 데 사용되는 정규식 매개 변수를 지정하는 하나 이상의 문자로 된 문자열입니다. 지원되는 값은 다음과 같습니다.

  • c: 대/소문자를 구분합니다.

  • i: 대/소문자를 구분하지 않습니다.

  • m: 여러 줄 모드입니다.

  • e: 하위 일치 항목을 추출합니다.

  • s: ‘.’ 와일드카드는 줄 바꿈과도 일치합니다.

자세한 내용은 정규식 매개 변수 설명서를 참조하십시오.

기본값: c

참고

기본적으로, REGEXP_INSTR은 주제의 전체 일치 부분에 대한 시작 또는 끝 문자 오프셋을 반환합니다. 그러나 e (“추출”의 경우) 매개 변수가 지정되면 REGEXP_INSTR은 패턴의 첫 번째 하위 식과 일치하는 주제 부분의 시작 또는 끝 문자 오프셋을 반환합니다. e 가 지정되었지만 group_num 도 지정되지 않은 경우, group_num 의 기본값은 1(첫 번째 그룹)입니다. 패턴에 하위 식이 없으면 REGEXP_INSTR은 e 가 설정되지 않은 것처럼 작동합니다. e 를 사용하는 예는 이 항목의 예 를 참조하십시오.

group_num

group_num 매개 변수는 추출할 그룹을 지정합니다. 그룹은 정규식에서 괄호를 사용하여 지정됩니다.

group_num 가 지정되면 Snowflake는 추출을 허용합니다. 'e' 옵션도 지정하지 않은 경우에도 마찬가지입니다. 'e' 는 내포되어 있습니다.

Snowflake는 최대 1024개의 그룹을 지원합니다.

group_num 를 사용하는 예는 이 항목의 예 를 참조하십시오.

사용법 노트

  • 위치는 0이 아니라 1부터 시작합니다. 예를 들어, “MAN”에서 문자 “M”의 위치는 0이 아니라 1입니다.

  • 추가적인 사용법 노트는 정규식 함수에 대한 일반적인 사용법 노트 을 참조하십시오.

데이터 정렬 세부 정보

Arguments with collation specifications are currently not supported.

예

기본 예

다음 몇 가지 예에서는 다음 데이터를 사용합니다.

CREATE TABLE demo1 (id INT, string1 VARCHAR);
INSERT INTO demo1 (id, string1) VALUES 
    (1, 'nevermore1, nevermore2, nevermore3.')
    ;
Copy

일치하는 문자열을 검색합니다. 이 경우, 문자열은 “nevermore” 뒤에 다음과 같이 단일 10진수가 옵니다(예: “nevermore1”).

select id, string1,
      regexp_substr(string1, 'nevermore\\d') AS "SUBSTRING", 
      regexp_instr( string1, 'nevermore\\d') AS "POSITION"
    from demo1
    order by id;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore1 |        1 |
+----+-------------------------------------+------------+----------+
Copy

일치하는 문자열을 검색하되, 문자열의 첫 번째 문자가 아니라 다음과 같이 문자열의 다섯 번째 문자에서 시작합니다.

select id, string1,
      regexp_substr(string1, 'nevermore\\d', 5) AS "SUBSTRING", 
      regexp_instr( string1, 'nevermore\\d', 5) AS "POSITION"
    from demo1
    order by id;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore2 |       13 |
+----+-------------------------------------+------------+----------+
Copy

일치하는 문자열을 검색하되, 첫 번째 일치 항목이 아니라 다음과 같이 세 번째 일치 항목을 찾습니다.

select id, string1,
      regexp_substr(string1, 'nevermore\\d', 1, 3) AS "SUBSTRING", 
      regexp_instr( string1, 'nevermore\\d', 1, 3) AS "POSITION"
    from demo1
    order by id;
+----+-------------------------------------+------------+----------+
| ID | STRING1                             | SUBSTRING  | POSITION |
|----+-------------------------------------+------------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |       25 |
+----+-------------------------------------+------------+----------+
Copy

이 쿼리는 이전 쿼리와 거의 동일하지만, 일치하는 식의 위치를 원하는지, 아니면 일치하는 식 뒤의 첫 번째 문자의 위치를 원하는지를 option 매개 변수를 사용하여 표시하는 방법을 보여줍니다.

select id, string1,
       regexp_substr(string1, 'nevermore\\d', 1, 3) AS "SUBSTRING", 
       regexp_instr( string1, 'nevermore\\d', 1, 3, 0) AS "START_POSITION",
       regexp_instr( string1, 'nevermore\\d', 1, 3, 1) AS "AFTER_POSITION"
    from demo1
    order by id;
+----+-------------------------------------+------------+----------------+----------------+
| ID | STRING1                             | SUBSTRING  | START_POSITION | AFTER_POSITION |
|----+-------------------------------------+------------+----------------+----------------|
|  1 | nevermore1, nevermore2, nevermore3. | nevermore3 |             25 |             35 |
+----+-------------------------------------+------------+----------------+----------------+
Copy

이 쿼리는 마지막 실제 발생 이후의 항목을 검색하는 경우 반환된 위치가 0임을 보여줍니다.

select id, string1, 
       regexp_substr(string1, 'nevermore', 1, 4) AS "SUBSTRING",
       regexp_instr( string1, 'nevermore', 1, 4) AS "POSITION"
    from demo1
    order by id;
+----+-------------------------------------+-----------+----------+
| ID | STRING1                             | SUBSTRING | POSITION |
|----+-------------------------------------+-----------+----------|
|  1 | nevermore1, nevermore2, nevermore3. | NULL      |        0 |
+----+-------------------------------------+-----------+----------+
Copy

캡처 그룹의 예

이 섹션에서는 정규식의 “그룹” 기능을 사용하는 방법을 보여줍니다.

이 섹션의 처음 몇 가지 예에서는 캡처 그룹을 사용하지 않습니다. 이 섹션은 몇 가지 간단한 예로 시작한 다음, 캡처 그룹을 사용하는 예로 계속됩니다.

이 예에서는 아래에 만들어진 문자열을 사용합니다.

CREATE TABLE demo2 (id INT, string1 VARCHAR);
INSERT INTO demo2 (id, string1) VALUES 
    -- A string with multiple occurrences of the word "the".
    (2, 'It was the best of times, it was the worst of times.'),
    -- A string with multiple occurrences of the word "the" and with extra
    -- blanks between words.
    (3, 'In    the   string   the   extra   spaces  are   redundant.'),
    -- A string with the character sequence "the" inside multiple words 
    -- ("thespian" and "theater"), but without the word "the" by itself.
    (4, 'A thespian theater is nearby.')
    ;
Copy

다음 예는 아래 항목을 찾습니다.

  • “the” 단어

  • 뒤에 오는 하나 이상의 비단어 문자(예: 단어를 구분하는 공백)

  • 뒤에 오는 하나 이상의 단어 문자.

“단어 문자”에는 문자 a-z 및 A-Z뿐만 아니라 밑줄(“_”) 및 10진수 0-9가 포함되지만 공백, 구두점 등은 포함되지 않습니다.

select id, string1,
       regexp_substr(string1, 'the\\W+\\w+') as "SUBSTRING",
       regexp_instr(string1, 'the\\W+\\w+') as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+--------------+----------+
| ID | STRING1                                                     | SUBSTRING    | POSITION |
|----+-------------------------------------------------------------+--------------+----------|
|  2 | It was the best of times, it was the worst of times.        | the best     |        8 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | the   string |        7 |
|  4 | A thespian theater is nearby.                               | NULL         |        0 |
+----+-------------------------------------------------------------+--------------+----------+
Copy

문자열의 위치 1에서 시작하여 다음 항목의 두 번째 발생을 찾습니다.

  • “the” 단어

  • 뒤에 오는 하나 이상의 비단어 문자

  • 뒤에 오는 하나 이상의 단어 문자.

select id, string1, 
       regexp_substr(string1, 'the\\W+\\w+', 1, 2) as "SUBSTRING",
       regexp_instr(string1, 'the\\W+\\w+', 1, 2) as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+-------------+----------+
| ID | STRING1                                                     | SUBSTRING   | POSITION |
|----+-------------------------------------------------------------+-------------+----------|
|  2 | It was the best of times, it was the worst of times.        | the worst   |       34 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | the   extra |       22 |
|  4 | A thespian theater is nearby.                               | NULL        |        0 |
+----+-------------------------------------------------------------+-------------+----------+
Copy

이 예는 앞의 예와 유사하지만, 캡처 그룹을 추가합니다. 전체 일치의 위치를 반환하는 대신, 이 쿼리는 “그룹”의 위치(즉 괄호 안의 정규식 부분과 일치하는 하위 문자열 부분)만 반환합니다. 이 경우, 반환된 값은 “the” 뒤에 오는 단어의 위치일 것입니다.

select id, string1,
       regexp_substr(string1, 'the\\W+(\\w+)', 1, 2,    'e', 1) as "SUBSTRING",
       regexp_instr( string1, 'the\\W+(\\w+)', 1, 2, 0, 'e', 1) as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+
Copy

다음과 같이 'e' (“추출”) 매개 변수를 지정하고 group_num 를 지정하지 않으면 그룹_숫자의 기본값은 1입니다.

select id, string1,
       regexp_substr(string1, 'the\\W+(\\w+)', 1, 2,    'e') as "SUBSTRING",
       regexp_instr( string1, 'the\\W+(\\w+)', 1, 2, 0, 'e') as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+
Copy

다음과 같이 group_num 를 지정하면 Snowflake는 사용자가 추출을 원한다고 가정합니다. 'e' (“추출”)를 매개 변수 중 하나로 지정하지 않은 경우에도 마찬가지입니다.

select id, string1,
       regexp_substr(string1, 'the\\W+(\\w+)', 1, 2,    '', 1) as "SUBSTRING",
       regexp_instr( string1, 'the\\W+(\\w+)', 1, 2, 0, '', 1) as "POSITION"
    from demo2
    order by id;
+----+-------------------------------------------------------------+-----------+----------+
| ID | STRING1                                                     | SUBSTRING | POSITION |
|----+-------------------------------------------------------------+-----------+----------|
|  2 | It was the best of times, it was the worst of times.        | worst     |       38 |
|  3 | In    the   string   the   extra   spaces  are   redundant. | extra     |       28 |
|  4 | A thespian theater is nearby.                               | NULL      |        0 |
+----+-------------------------------------------------------------+-----------+----------+
Copy

이 예는 첫 번째 단어가 A 인, 두 단어로 된 패턴의 첫 번째, 두 번째, 세 번째 일치 항목에서 두 번째 단어의 위치를 검색하는 방법을 보여줍니다. 이는 마지막 패턴을 넘어서려고 하면 Snowflake가 0을 반환한다는 것도 보여줍니다.

CREATE TABLE demo3 (id INT, string1 VARCHAR);
INSERT INTO demo3 (id, string1) VALUES
    (5, 'A MAN A PLAN A CANAL')
    ;
Copy
select id, string1,
    regexp_substr(string1, 'A\\W+(\\w+)', 1, 1,    'e', 1) as "SUBSTRING1",
    regexp_instr( string1, 'A\\W+(\\w+)', 1, 1, 0, 'e', 1) as "POSITION1",
    regexp_substr(string1, 'A\\W+(\\w+)', 1, 2,    'e', 1) as "SUBSTRING2",
    regexp_instr( string1, 'A\\W+(\\w+)', 1, 2, 0, 'e', 1) as "POSITION2",
    regexp_substr(string1, 'A\\W+(\\w+)', 1, 3,    'e', 1) as "SUBSTRING3",
    regexp_instr( string1, 'A\\W+(\\w+)', 1, 3, 0, 'e', 1) as "POSITION3",
    regexp_substr(string1, 'A\\W+(\\w+)', 1, 4,    'e', 1) as "SUBSTRING4",
    regexp_instr( string1, 'A\\W+(\\w+)', 1, 4, 0, 'e', 1) as "POSITION4"
    from demo3;
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+
| ID | STRING1              | SUBSTRING1 | POSITION1 | SUBSTRING2 | POSITION2 | SUBSTRING3 | POSITION3 | SUBSTRING4 | POSITION4 |
|----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------|
|  5 | A MAN A PLAN A CANAL | MAN        |         3 | PLAN       |         9 | CANAL      |        16 | NULL       |         0 |
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+
Copy

이 예는 패턴의 첫 번째 발생 내에서 첫 번째, 두 번째, 세 번째 그룹의 위치를 검색하는 방법을 보여줍니다. 이 경우, 반환된 값은 MAN 단어의 개별 문자의 위치입니다.

select id, string1, 
    regexp_substr(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 1) as "SUBSTR1",
    regexp_instr( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 1) as "POS1",
    regexp_substr(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 2) as "SUBSTR2",
    regexp_instr( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 2) as "POS2",
    regexp_substr(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 3) as "SUBSTR3",
    regexp_instr( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 3) as "POS3"
    from demo3;
+----+----------------------+---------+------+---------+------+---------+------+
| ID | STRING1              | SUBSTR1 | POS1 | SUBSTR2 | POS2 | SUBSTR3 | POS3 |
|----+----------------------+---------+------+---------+------+---------+------|
|  5 | A MAN A PLAN A CANAL | M       |    3 | A       |    4 | N       |    5 |
+----+----------------------+---------+------+---------+------+---------+------+
Copy

추가 예

다음 예는 was 단어의 발생과 일치합니다. 일치는 문자열의 첫 번째 문자에서 시작하며, 문자열에서 첫 번째 발생 다음에 오는 문자의 위치를 반환합니다.

select regexp_instr('It was the best of times, it was the worst of times', '\\bwas\\b', 1, 1) as "result" from dual;

+--------+
| result |
|--------|
|      4 |
+--------+
Copy

다음 예에서는 패턴과 일치하는 문자열 부분의 첫 번째 문자 오프셋을 반환합니다. 일치는 문자열의 첫 번째 문자에서 시작하며, 패턴의 첫 번째 발생을 반환합니다.

select regexp_instr('It was the best of times, it was the worst of times', 'the\\W+(\\w+)',1,1,0) as "result" from dual;

+--------+
| result |
|--------|
|      8 |
+--------+
Copy

다음 예는 이전 예와 동일하지만, e 매개 변수를 사용하여, 패턴의 첫 번째 하위 식과 일치하는 주제 부분(즉, the 다음의 첫 번째 단어 문자 세트)에 대한 문자 오프셋을 반환합니다.

select regexp_instr('It was the best of times, it was the worst of times', 'the\\W+(\\w+)',1,1,0,'e') as "result" from dual;

+--------+
| result |
|--------|
|     12 |
+--------+
Copy

다음 예는 2개 이상의 알파벳 문자(대/소문자 구분 안 함)가 앞에 오는, st 로 끝나는 단어를 찾습니다. 일치는 문자열의 열다섯 번째 문자에서 시작하며, 문자열에서 첫 번째 발생 다음에 오는 문자의 위치를 반환합니다.

select regexp_instr('It was the best of times, it was the worst of times', '[[:alpha:]]{2,}st', 15, 1) as "result" from dual;

+--------+
| result |
|--------|
|     38 |
+--------+
Copy

다음과 같이 예를 준비합니다.

create or replace table message(body varchar(255));
insert into message values
('Hellooo World'),
('How are you doing today?'),
('the quick brown fox jumps over the lazy dog'),
('PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS');
Copy

다음과 같이 첫 번째 일치 항목에서 첫 번째 문자의 오프셋을 반환합니다.

select body, regexp_instr(body, '\\b\\S*o\\S*\\b') as result from message;

---------------------------------------------+-----------------------------------+
                    body                     | result                            |
---------------------------------------------+-----------------------------------+
 Hellooo World                               | 1                                 |
 How are you doing today?                    | 1                                 |
 the quick brown fox jumps over the lazy dog | 11                                |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 0                                 |
---------------------------------------------+-----------------------------------+
Copy

주제의 세 번째 문자에서 시작하여 첫 번째 일치 항목의 첫 번째 문자:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3) as result from message;

---------------------------------------------+--------------------------------------+
                    body                     | result                               |
---------------------------------------------+--------------------------------------+
 Hellooo World                               | 3                                    |
 How are you doing today?                    | 9                                    |
 the quick brown fox jumps over the lazy dog | 11                                   |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 0                                    |
---------------------------------------------+--------------------------------------+
Copy

주제의 세 번째 문자에서 시작하여 세 번째 일치 항목의 첫 번째 문자:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3, 3) as result from message;

---------------------------------------------+-----------------------------------------+
                    body                     | result                                  |
---------------------------------------------+-----------------------------------------+
 Hellooo World                               | 0                                       |
 How are you doing today?                    | 19                                      |
 the quick brown fox jumps over the lazy dog | 27                                      |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 0                                       |
---------------------------------------------+-----------------------------------------+
Copy

주제의 세 번째 문자에서 시작하여 세 번째 일치 항목의 마지막 문자:

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3, 3, 1) as result from message;

---------------------------------------------+--------------------------------------------+
                    body                     | result                                     |
---------------------------------------------+--------------------------------------------+
 Hellooo World                               | 0                                          |
 How are you doing today?                    | 24                                         |
 the quick brown fox jumps over the lazy dog | 31                                         |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 0                                          |
---------------------------------------------+--------------------------------------------+
Copy

주제의 세 번째 문자에서 시작하여 세 번째 일치 항목의 마지막 문자(대/소문자 구분하지 않는 일치):

select body, regexp_instr(body, '\\b\\S*o\\S*\\b', 3, 3, 1, 'i') as result from message;

---------------------------------------------+-------------------------------------------------+
                    body                     | result                                          |
---------------------------------------------+-------------------------------------------------+
 Hellooo World                               | 0                                               |
 How are you doing today?                    | 24                                              |
 the quick brown fox jumps over the lazy dog | 31                                              |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 35                                              |
---------------------------------------------+-------------------------------------------------+
Copy
select body, regexp_instr(body, '\\S*(o)\\S*\\b', 1, 1, 0, 'i') as result from message;

---------------------------------------------+-------------------------------------------------+
                    body                     | result                                          |
---------------------------------------------+-------------------------------------------------+
 Hellooo World                               | 1                                               |
 How are you doing today?                    | 1                                               |
 the quick brown fox jumps over the lazy dog | 11                                              |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 9                                               |
---------------------------------------------+-------------------------------------------------+
Copy
select body, regexp_instr(body, '\\S*(o)\\S*\\b', 1, 1, 0, 'ie') as result from message;

---------------------------------------------+--------------------------------------------------+
                    body                     | result                                           |
---------------------------------------------+--------------------------------------------------+
 Hellooo World                               | 7                                                |
 How are you doing today?                    | 2                                                |
 the quick brown fox jumps over the lazy dog | 13                                               |
 PACK MY BOX WITH FIVE DOZEN LIQUOR JUGS     | 10                                               |
---------------------------------------------+--------------------------------------------------+
Copy
언어: 한국어