- 카테고리:
문자열 및 이진 함수 (일치/비교)
SUBSTR , SUBSTRING¶
base_expr 에 의해 지정된 문자/바이트에서 시작하여 start_expr 에서 문자열 또는 이진 값의 부분을, 선택적으로 제한된 길이로 반환합니다.
이러한 함수는 동의어입니다.
구문¶
SUBSTR( <base_expr>, <start_expr> [ , <length_expr> ] )
SUBSTRING( <base_expr>, <start_expr> [ , <length_expr> ] )
인자¶
base_exprVARCHAR 또는 BINARY 값으로 평가되는 식입니다.
start_expr정수로 평가되는 식입니다. 하위 문자열이 시작되는 오프셋을 지정합니다. 오프셋은 다음으로 측정됩니다.
입력이 VARCHAR 값인 경우 UTF-8 문자 수.
입력이 BINARY 값인 경우 바이트 수.
시작 위치는 0이 아니라 1부터 시작합니다. 예를 들어,
SUBSTR('abc', 1, 1)은b가 아니라a를 반환합니다.length_expr정수로 평가되는 식입니다. 이 식에서 지정하는 사항은 다음과 같습니다.
입력이 VARCHAR 인 경우 반환할 UTF-8 문자 수입니다.
입력이 BINARY 인 경우 반환할 바이트 수입니다.
0보다 크거나 같은 길이를 지정합니다. 길이가 음수이면 함수는 빈 문자열을 반환합니다.
반환¶
반환된 값의 데이터 타입은 base_expr (VARCHAR 또는 BINARY)의 데이터 타입과 동일합니다.
입력 중 하나라도 NULL인 경우, NULL이 반환됩니다.
사용법 노트¶
length_expr이 지정되면 최대length_expr개의 문자/바이트가 반환됩니다.length_expr이 지정되지 않으면 문자열 끝까지의 모든 문자나 이진 값이 반환됩니다.start_expr의 값은 1부터 시작합니다.0을 지정하면 1로 처리됩니다.
음수 값이 지정되는 경우, 시작 위치는 문자열 또는 이진 값의 끝에서
start_expr문자/바이트로 계산됩니다. 위치가 문자열 또는 이진 값의 범위를 벗어나는 경우, 빈 값이 반환됩니다.
데이터 정렬 세부 정보¶
데이터 정렬은 VARCHAR 입력에 적용됩니다. 첫 번째 매개 변수의 입력 데이터 타입이 BINARY인 경우 데이터 정렬이 적용되지 않습니다.
No impact. 데이터 정렬이 구문적으로 허용되지만, 데이터 정렬은 처리에 영향을 주지 않습니다. 예를 들어, 언어에서 2자 및 3자로 구성된 문자(예: 헝가리어의 “dzs”, 체코어의 “ch”)라도 여전히 길이 인자에 대해 2개 또는 3개의 문자(한 문자가 아님)로 계산됩니다.
The collation of the result is the same as the collation of the input. 이는 반환된 값이 중첩 함수 호출의 일부로서 다른 함수에 전달되는 경우에 유용할 수 있습니다.
예¶
다음 예제에서는 SUBSTR 함수를 사용합니다.
기본 예제¶
다음 예에서는 SUBSTR 함수를 사용하여 9번째 문자에서 시작하는 문자열 부분을 반환하고 반환된 값의 길이를 3자로 제한합니다.
SELECT SUBSTR('testing 1 2 3', 9, 3);
+-------------------------------+
| SUBSTR('TESTING 1 2 3', 9, 3) |
|-------------------------------|
| 1 2 |
+-------------------------------+
다른 시작 값 및 길이 값 지정하기¶
다음 예에서는 start_expr 및 length_expr 에 대해 서로 다른 값이 지정된 경우 동일한 base_expr 에 대해 반환되는 하위 문자열을 보여줍니다.
CREATE OR REPLACE TABLE test_substr (
base_value VARCHAR,
start_value INT,
length_value INT)
AS SELECT
column1,
column2,
column3
FROM
VALUES
('mystring', -1, 3),
('mystring', -3, 3),
('mystring', -3, 7),
('mystring', -5, 3),
('mystring', -7, 3),
('mystring', 0, 3),
('mystring', 0, 7),
('mystring', 1, 3),
('mystring', 1, 7),
('mystring', 3, 3),
('mystring', 3, 7),
('mystring', 5, 3),
('mystring', 5, 7),
('mystring', 7, 3),
('mystring', NULL, 3),
('mystring', 3, NULL);
SELECT base_value,
start_value,
length_value,
SUBSTR(base_value, start_value, length_value) AS substring
FROM test_substr;
+------------+-------------+--------------+-----------+
| BASE_VALUE | START_VALUE | LENGTH_VALUE | SUBSTRING |
|------------+-------------+--------------+-----------|
| mystring | -1 | 3 | g |
| mystring | -3 | 3 | ing |
| mystring | -3 | 7 | ing |
| mystring | -5 | 3 | tri |
| mystring | -7 | 3 | yst |
| mystring | 0 | 3 | mys |
| mystring | 0 | 7 | mystrin |
| mystring | 1 | 3 | mys |
| mystring | 1 | 7 | mystrin |
| mystring | 3 | 3 | str |
| mystring | 3 | 7 | string |
| mystring | 5 | 3 | rin |
| mystring | 5 | 7 | ring |
| mystring | 7 | 3 | ng |
| mystring | NULL | 3 | NULL |
| mystring | 3 | NULL | NULL |
+------------+-------------+--------------+-----------+
이메일, 전화번호 및 날짜 문자열의 하위 문자열 반환하기¶
다음 예에서는 테이블의 고객 정보에 대한 하위 문자열을 반환합니다.
테이블을 만들고 데이터를 삽입합니다.
CREATE OR REPLACE TABLE customer_contact_example (
cust_id INT,
cust_email VARCHAR,
cust_phone VARCHAR,
activation_date VARCHAR)
AS SELECT
column1,
column2,
column3,
column4
FROM
VALUES
(1, 'some_text@example.com', '800-555-0100', '20210320'),
(2, 'some_other_text@example.org', '800-555-0101', '20240509'),
(3, 'some_different_text@example.net', '800-555-0102', '20191017');
SELECT * from customer_contact_example;
+---------+---------------------------------+--------------+-----------------+
| CUST_ID | CUST_EMAIL | CUST_PHONE | ACTIVATION_DATE |
|---------+---------------------------------+--------------+-----------------|
| 1 | some_text@example.com | 800-555-0100 | 20210320 |
| 2 | some_other_text@example.org | 800-555-0101 | 20240509 |
| 3 | some_different_text@example.net | 800-555-0102 | 20191017 |
+---------+---------------------------------+--------------+-----------------+
POSITION 함수와 SUBSTR 함수를 함께 사용하여 이메일 주소에서 도메인을 추출합니다. 다음 예에서는 각 문자열에서 @ 의 위치를 찾고 다음 문자부터 1을 더합니다.
SELECT cust_id,
cust_email,
SUBSTR(cust_email, POSITION('@' IN cust_email) + 1) AS domain
FROM customer_contact_example;
+---------+---------------------------------+-------------+
| CUST_ID | CUST_EMAIL | DOMAIN |
|---------+---------------------------------+-------------|
| 1 | some_text@example.com | example.com |
| 2 | some_other_text@example.org | example.org |
| 3 | some_different_text@example.net | example.net |
+---------+---------------------------------+-------------+
팁
POSITION 함수를 사용하면 빈 문자(' ')나 밑줄(_)과 같은 다른 문자의 위치를 찾을 수 있습니다.
테이블의 cust_phone 열에서는 항상 처음 세 문자가 지역 번호입니다. 전화 번호에서 지역 번호를 추출합니다.
SELECT cust_id,
cust_phone,
SUBSTR(cust_phone, 1, 3) AS area_code
FROM customer_contact_example;
+---------+--------------+-----------+
| CUST_ID | CUST_PHONE | AREA_CODE |
|---------+--------------+-----------|
| 1 | 800-555-0100 | 800 |
| 2 | 800-555-0101 | 800 |
| 3 | 800-555-0102 | 800 |
+---------+--------------+-----------+
전화 번호에서 지역 번호를 제거합니다.
SELECT cust_id,
cust_phone,
SUBSTR(cust_phone, 5) AS phone_without_area_code
FROM customer_contact_example;
+---------+--------------+-------------------------+
| CUST_ID | CUST_PHONE | PHONE_WITHOUT_AREA_CODE |
|---------+--------------+-------------------------|
| 1 | 800-555-0100 | 555-0100 |
| 2 | 800-555-0101 | 555-0101 |
| 3 | 800-555-0102 | 555-0102 |
+---------+--------------+-------------------------+
테이블의 activation_date 열에서 날짜는 항상 YYYYMMDD 형식으로 되어 있습니다. 다음 문자열에서 연도, 월, 일을 추출합니다.
SELECT cust_id,
activation_date,
SUBSTR(activation_date, 1, 4) AS year,
SUBSTR(activation_date, 5, 2) AS month,
SUBSTR(activation_date, 7, 2) AS day
FROM customer_contact_example;
+---------+-----------------+------+-------+-----+
| CUST_ID | ACTIVATION_DATE | YEAR | MONTH | DAY |
|---------+-----------------+------+-------+-----|
| 1 | 20210320 | 2021 | 03 | 20 |
| 2 | 20240509 | 2024 | 05 | 09 |
| 3 | 20191017 | 2019 | 10 | 17 |
+---------+-----------------+------+-------+-----+