REGEXP_INSTR
REGEXP_INSTR
REGEXP_INSTR 함수는 문자열 subject에서 정규식 패턴의 지정된 출현(occurrence) 위치를 반환해요.
참고: 문자열 함수 (정규식)(String functions (regular expressions))도 참조하세요.
출처: 문서
본문
문법 (Syntax)
REGEXP_INSTR( <subject> , <pattern> [ , <position> [ , <occurrence> [ , <option> [ , <regexp_parameters> [ , <group_num> ] ] ] ] ] )
인자 (Arguments)
필수 (Required):
- subject — 일치 항목을 검색할 문자열이에요.
- pattern — 일치시킬 패턴이에요. 패턴 지정에 대한 지침은 문자열 함수 (정규식)을 참조하세요.
선택 (Optional):
-
position — 함수가 일치 항목 검색을 시작하는 문자열의 시작부터의 문자 수예요. 값은 양의 정수여야 해요. 기본값: 1 (일치 검색은 왼쪽의 첫 번째 문자에서 시작).
-
occurrence — 일치 항목 반환을 시작할 패턴의 첫 번째 출현을 지정해요. 함수는 첫 번째
occurrence - 1개 일치 항목을 건너뛰어요. 예를 들어 일치 항목이 5개이고 occurrence 인자에 3을 지정하면 함수는 처음 두 일치 항목을 무시하고 세 번째, 네 번째, 다섯 번째 일치 항목을 반환해요. 기본값: 1. -
option — 일치 항목의 첫 번째 문자의 오프셋(0)을 반환할지, 일치 항목의 끝 다음 첫 번째 문자의 오프셋(1)을 반환할지 지정해요. 기본값: 0.
-
regexp_parameters — 일치 항목 검색에 사용되는 파라미터를 지정하는 하나 이상의 문자로 이루어진 문자열이에요. 지원되는 값:
파라미터 (Parameter) 설명 (Description) c 대소문자 구분 일치 (Case-sensitive matching) i 대소문자 비구분 일치 (Case-insensitive matching) m 다중 행 모드 (Multi-line mode) e 하위 일치 추출 (Extract submatches) s 단일 행 모드, POSIX 와일드카드 문자 .가\n과 일치기본값: c
자세한 내용은 정규식 파라미터 지정(Specifying the parameters for the regular expression)을 참조하세요.
참고: 기본적으로 REGEXP_INSTR은 subject의 일치하는 전체 부분에 대한 시작 또는 끝 문자 오프셋을 반환해요. 그러나 e("extract") 파라미터를 지정하면 REGEXP_INSTR은 패턴의 첫 번째 하위 표현식과 일치하는 subject 부분에 대한 시작 또는 끝 문자 오프셋을 반환해요. e를 지정했지만 group_num도 지정하지 않으면 group_num은 기본값 1(첫 번째 그룹)이 돼요. 패턴에 하위 표현식이 없으면 REGEXP_INSTR은 e가 설정되지 않은 것처럼 동작해요. e를 사용하는 예제는 이 주제의 예제(Examples)를 참조하세요.
-
group_num — group_num 파라미터는 추출할 그룹을 지정해요. 그룹은 정규식에서 괄호를 사용해 지정돼요. group_num을 지정하면 e 옵션도 지정하지 않았어도 Snowflake는 추출을 허용해요. e 옵션은 암시적으로 지정된 것으로 간주돼요. Snowflake는 최대 1024개 그룹을 지원해요. group_num을 사용하는 예제는 이 주제의 캡처 그룹 예제(Examples of capture groups)를 참조하세요.
반환 (Returns)
NUMBER 타입의 값을 반환해요. 일치 항목을 찾지 못하면 0을 반환해요.
사용 메모 (Usage notes)
- 위치는 0이 아닌 1부터 시작해요. 예를 들어 "MAN"에서 문자 "M"의 위치는 0이 아니라 1이에요.
- 추가 사용 메모는 정규식 함수에 대한 일반 사용 메모(General usage notes for regular expression functions)를 참조하세요.
대조(collation) 세부사항
2026_04 BCR 번들이 활성화되면 이 함수는 대조 지정이 있는 인자를 받아들여요. 대조는 패턴 일치에 영향을 주지 않아요. 대조는 파라미터 인자에 i 플래그를 전달하지 않는 한 항상 대소문자를 구분해요.
예제 (Examples)
다음 예제들은 REGEXP_INSTR 함수를 사용해요.
기본 예제 (Basic examples)
테이블을 만들고 데이터를 삽입해요:
CREATE OR REPLACE TABLE demo1 (id INT, string1 VARCHAR);
INSERT INTO demo1 (id, string1) VALUES
(1, 'nevermore1, nevermore2, nevermore3.');
일치하는 문자열을 검색해요. 이 경우 문자열은 nevermore 뒤에 한 자리 숫자가 오는 형태(예: 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 |
+----+-------------------------------------+-----------+----------+
캡처 그룹 예제 (Examples of capture groups)
이 섹션은 정규식의 "그룹" 기능을 사용하는 방법을 보여줘요. 이 섹션의 처음 몇 예제는 캡처 그룹을 사용하지 않아요. 이 섹션은 몇 가지 간단한 예제로 시작한 다음 캡처 그룹을 사용하는 예제로 이어져요.
이 예제들은 아래에 생성된 문자열들을 사용해요:
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라는 단어의 첫 번째 출현, 그 뒤의 하나 이상의 비단어 문자(예: 단어를 구분하는 공백), 그 뒤의 하나 이상의 단어 문자를 찾아요.
"단어 문자(Word characters)"에는 a-z와 A-Z 문자뿐만 아니라 밑줄("_")과 십진수 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'(extract) 파라미터를 지정하지만 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을 지정하면 'e'(extract)를 파라미터 중 하나로 지정하지 않았어도 Snowflake는 추출하려는 것으로 간주해요:
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 |
+----+----------------------+------------+-----------+------------+-----------+------------+-----------+
추가 예제 (Additional examples)
다음 예제는 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 뒤의 첫 번째 단어 문자 집합)과 일치하는 subject 부분의 문자 오프셋을 반환해요:
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 |
+--------+
다음 예제는 두 개 이상의 알파벳 문자 뒤에 st로 끝나는 단어의 출현과 일치해요(대소문자 비구분). 일치는 문자열의 열다섯 번째 문자에서 시작하며 첫 번째 출현 다음 문자의 문자열 내 위치(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 |
+---------------------------------------------+--------+
subject의 세 번째 문자에서 시작해 소문자 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 |
+---------------------------------------------+--------+
subject의 세 번째 문자에서 시작해 소문자 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 |
+---------------------------------------------+--------+
subject의 세 번째 문자에서 시작해 소문자 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 |
+---------------------------------------------+--------+
대소문자 비구분 일치로, subject의 세 번째 문자에서 시작해 소문자 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 |
+---------------------------------------------+--------+