카테고리:: 문자열 함수(정규식)

REGEXP_INSTR¶

문자열 주제에서 정규식 패턴의 지정된 발생 위치를 반환합니다.

구문¶

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

인자¶

필수:

subject

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

pattern

일치할 패턴입니다.

패턴 지정에 관한 지침은 문자열 함수(정규식) 섹션을 참조하십시오.

선택 사항:

position

함수가 일치 항목 검색을 시작하는 문자열 시작 부분의 문자 수입니다. 값은 양의 정수여야 합니다.

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

occurrence

일치 항목을 반환하기 시작할 첫 번째 패턴 항목을 지정합니다.

함수는 첫 occurrence - 1 개의 일치 항목을 건너뜁니다. 예를 들어 5개의 일치 항목이 있고 occurrence 인자에 대해 3 을 지정할 경우 이 함수는 처음 두 일치 항목은 무시하고 세 번째, 네 번째, 다섯 번째 일치 항목을 반환합니다.

기본값: 1

option

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

기본값: 0

regexp_parameters

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


매개 변수	설명
`c`	대/소문자를 구분하는 일치
`i`	대/소문자를 구분하지 않는 일치
`m`	여러 줄 모드
`e`	하위 일치 항목 추출
`s`	한 줄 모드 POSIX 와일드카드 문자 `.` 는 `\n` 과 일치합니다.

기본값: 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 을 사용하는 예는 이 항목의 캡처 그룹의 예 섹션을 참조하십시오.

반환¶

NUMBER 형식의 값을 반환합니다.

일치하는 항목이 없으면 0 을 반환합니다.

사용법 노트¶

위치는 0이 아니라 1부터 시작합니다. 예를 들어, “MAN”에서 문자 “M”의 위치는 0이 아니라 1입니다.
추가적인 사용법 노트는 정규식 함수에 대한 일반적인 사용법 노트 을 참조하십시오.

데이터 정렬 세부 정보¶

Arguments with collation specifications currently aren’t supported.

예¶

다음 예제에서는 REGEXP_INSTR 함수를 사용합니다.

기본 예제¶

테이블을 만들고 데이터를 삽입합니다.

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

일치하는 문자열을 검색합니다. 이 경우, 문자열은 nevermore 뒤에 단일 10진수가 옵니다(예: nevermore1). 이 예에서는 REGEXP_SUBSTR 함수를 사용하여 일치하는 하위 문자열을 표시합니다.

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 |
+----+-------------------------------------+------------+----------+

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

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 |
+----+-------------------------------------+------------+----------+

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

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 |
+----+-------------------------------------+------------+----------+

이 쿼리는 이전 쿼리와 거의 동일하지만, 일치하는 식의 위치를 원하는지, 아니면 일치하는 식 뒤의 첫 번째 문자의 위치를 원하는지를 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 |
+----+-------------------------------------+------------+----------------+----------------+

이 쿼리는 마지막 실제 발생 이후의 항목을 검색하는 경우 반환된 위치가 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 |
+----+-------------------------------------+-----------+----------+

캡처 그룹의 예¶

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

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

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

CREATE OR REPLACE TABLE demo2 (id INT, string1 VARCHAR);

INSERT INTO demo2 (id, string1) VALUES
    (2, 'It was the best of times, it was the worst of times.'),
    (3, 'In    the   string   the   extra   spaces  are   redundant.'),
    (4, 'A thespian theater is nearby.');

SELECT * FROM demo2;

+----+-------------------------------------------------------------+
| ID | STRING1                                                     |
|----+-------------------------------------------------------------|
|  2 | It was the best of times, it was the worst of times.        |
|  3 | In    the   string   the   extra   spaces  are   redundant. |
|  4 | A thespian theater is nearby.                               |
+----+-------------------------------------------------------------+

문자열은 다음과 같은 특징이 있습니다.

id 가 2 인 문자열에는 “the”라는 단어가 여러 번 나옵니다.
id 가 3 인 문자열에는 “the”라는 단어가 여러 번 나오고 단어 사이에 공백이 추가로 있습니다.
id 가 4 인 문자열에는 여러 단어(“thespian”과 “theater”) 내에 문자 시퀀스 “the”가 있지만, 그 자체로 “the”라는 단어는 없습니다.

이 예에서는 단어 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 |
+----+-------------------------------------------------------------+--------------+----------+

문자열의 위치 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 |
+----+-------------------------------------------------------------+-------------+----------+

이 예는 앞의 예와 유사하지만, 캡처 그룹을 추가합니다. 전체 일치의 위치를 반환하는 대신, 이 쿼리는 그룹 의 위치(괄호 안의 정규식 부분과 일치하는 하위 문자열 부분)만 반환합니다. 이 경우, 반환된 값은 단어 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 |
+----+-------------------------------------------------------------+-----------+----------+

다음과 같이 'e' (추출) 매개 변수를 지정하고 group_num 을 지정하지 않으면 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 |
+----+-------------------------------------------------------------+-----------+----------+

다음과 같이 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 |
+----+-------------------------------------------------------------+-----------+----------+

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

테이블을 만들고 데이터를 삽입합니다.

CREATE TABLE demo3 (id INT, string1 VARCHAR);
INSERT INTO demo3 (id, string1) VALUES
  (5, 'A MAN A PLAN A CANAL');

쿼리를 실행합니다.

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 |
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+------------+-----------+

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

SELECT id,
       string1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 1) AS substring1,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 1) AS position1,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 2) AS substring2,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 2) AS position2,
       REGEXP_SUBSTR(string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1,    'e', 3) AS substring3,
       REGEXP_INSTR( string1, 'A\\W+(\\w)(\\w)(\\w)', 1, 1, 0, 'e', 3) AS position3
  FROM demo3;

+----+----------------------+------------+-----------+------------+-----------+------------+-----------+
| ID | STRING1              | SUBSTRING1 | POSITION1 | SUBSTRING2 | POSITION2 | SUBSTRING3 | POSITION3 |
|----+----------------------+------------+-----------+------------+-----------+------------+-----------|
|  5 | A MAN A PLAN A CANAL | M          |         3 | A          |         4 | N          |         5 |
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+

추가 예제¶

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

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    '\\bwas\\b',
                    1,
                    1) AS result;

+--------+
| RESULT |
|--------|
|      4 |
+--------+

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

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    'the\\W+(\\w+)',
                    1,
                    1,
                    0) AS result;

+--------+
| RESULT |
|--------|
|      8 |
+--------+

다음 예는 이전 예와 동일하지만, 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;

+--------+
| RESULT |
|--------|
|     12 |
+--------+

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

SELECT REGEXP_INSTR('It was the best of times, it was the worst of times',
                    '[[:alpha:]]{2,}st',
                    15,
                    1) AS result;

+--------+
| RESULT |
|--------|
|     38 |
+--------+

다음 예제 세트를 실행하려면 테이블을 만들고 데이터를 삽입하십시오.

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');

다음과 같이 소문자 o 를 포함하는 첫 번째 일치 항목에서 첫 번째 문자의 오프셋을 반환합니다.

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 |
+---------------------------------------------+--------+

다음과 같이 제목의 세 번째 문자부터 시작해 소문자 o 를 포함하는 첫 번째 일치 항목에서 첫 번째 문자의 오프셋을 반환합니다.

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 |
+---------------------------------------------+--------+

다음과 같이 제목의 세 번째 문자부터 시작해 소문자 o 를 포함하는 세 번째 일치 항목에서 첫 번째 문자의 오프셋을 반환합니다.

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 |
+---------------------------------------------+--------+

다음과 같이 제목의 세 번째 문자부터 시작해 소문자 o 를 포함하는 세 번째 일치 항목에서 마지막 문자의 오프셋을 반환합니다.

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 |
+---------------------------------------------+--------+

다음과 같이 대/소문자를 구분하지 않고 제목의 세 번째 문자부터 시작해 소문자 o 를 포함하는 세 번째 일치 항목에서 마지막 문자의 오프셋을 반환합니다.

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 |
+---------------------------------------------+--------+