STRING_SPLIT(Transact-SQL)

아티클
03/03/2023
읽는 데 10분 걸림

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

지정된 구분 기호 문자에 따라 문자열을 부분 문자열의 행으로 분할하는 테이블 반환 함수입니다.

호환성 수준 130

STRING_SPLIT에는 130 이상의 호환성 수준이 필요합니다. 130 미만 수준인 경우 SQL Server는 STRING_SPLIT 함수를 찾을 수 없습니다.

데이터베이스의 호환성 수준을 변경하려면 데이터베이스의 호환성 수준 보기 또는 변경을 참조합니다.

참고

Azure Synapse Analytics의 STRING_SPLIT에는 호환성 구성이 필요하지 않습니다.

Transact-SQL 구문 표기 규칙

구문

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

인수

string

모든 문자 형식(예: nvarchar, varchar, nchar 또는 char)의 식입니다.

separator

연결된 부분 문자열의 구분 기호로 사용되는 모든 문자 형식(예: nvarchar(1) , varchar(1) , nchar(1) 또는 char(1) )의 단일 문자 식입니다.

enable_ordinal

ordinal 출력 열을 사용하거나 사용하지 않도록 설정하는 플래그로 사용되는 int 또는 bit식입니다. 값 1은 ordinal 열을 사용하도록 설정합니다. enable_ordinal이 생략되거나 NULL이거나 값이 0이면 ordinal 열이 비활성화됩니다.

참고

enable_ordinal 인수 및 ordinal 출력 열은 현재 Azure SQL Database, Azure SQL Managed Instance 및 Azure Synapse Analytics(서버리스 SQL 풀에만 해당)에서 지원됩니다. SQL Server 2022(16.x)부터 SQL Server에서 인수 및 출력 열을 사용할 수 있습니다.

반환 형식

ordinal 출력 열을 사용하지 않도록 설정한 경우 STRING_SPLIT은 행이 부분 문자열인 단일 열 테이블을 반환합니다. 열 이름은 value입니다. 입력 인수가 nvarchar 또는 nchar인 경우 nvarchar를 반환합니다. 그렇지 않으면 varchar를 반환합니다. 반환 형식의 길이는 string 인수의 길이와 동일합니다.

enable_ordinal 인수에 값 1이 전달되면 입력 문자열에 있는 각 부분 문자열 위치의 1부터 시작하는 인덱스 값으로 구성된 ordinal이라는 두 번째 열이 반환됩니다. 반환 형식은 bigint입니다.

설명

STRING_SPLIT은 부분 문자열을 구분 기호로 분리한 문자열을 입력하고, 구분 기호로 사용할 문자 하나를 입력합니다. 필요에 따라 함수는 각각 ordinal 출력 열을 사용하지 않거나 사용하도록 설정하는 0 또는 1 값이 있는 세 번째 인수를 지원합니다.

STRING_SPLIT은 enable_ordinal 인수에 따라 단일 열 또는 이중 열 테이블을 출력합니다.

enable_ordinal이 NULL이거나 생략되거나 값이 0이면 STRING_SPLIT은 행에 부분 문자열이 포함된 단일 열 테이블을 반환합니다. 출력 열의 이름은 value입니다.
enable_ordinal 값이 1이면 함수는 원래 입력 문자열에 있는 부분 문자열의 1부터 시작하는 인덱스 값으로 구성된 ordinal 열이 포함된 2열 테이블을 반환합니다.

enable_ordinal 인수는 열이나 변수가 아닌 상수 값이어야 합니다. 또한 값이 0 또는 1인 bit 또는 int 데이터 형식이어야 합니다. 그렇지 않으면 함수에서 오류가 발생합니다.

출력 행은 순서에 관계 없을 수 있습니다. 순서가 입력 문자열의 부분 문자열 순서와 일치하지 않을 수 있습니다. SELECT 문에서 ORDER BY 절을 사용하여 최종 정렬 순서를 재정의할 수 있습니다(예: ORDER BY value 또는 ORDER BY ordinal).

0x0000(char(0) )은 Windows 데이터 정렬에서 정의되지 않은 문자이며 STRING_SPLIT에 포함할 수 없습니다.

0 길이의 빈 부분 문자열은 입력 문자열에서 구분 기호 문자 두 개 이상이 연속되는 경우 존재합니다. 빈 부분 문자열은 일반 부분 문자열과 동일한 방식으로 처리됩니다. WHERE 절을 사용하여 빈 부분 문자열을 포함하는 모든 행을 필터링할 수 있습니다(예: WHERE value <> ''). 입력 문자열이 NULL인 경우 STRING_SPLIT 테이블 반환 함수는 빈 테이블을 반환합니다.

예를 들어 다음 SELECT 문은 구분 기호로 공백 문자를 사용합니다.

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

실제 실행에서 앞의 SELECT 문은 다음과 같은 결과 테이블을 반환했습니다.

value
Lorem
ipsum
dolor
sit
amet.

다음 예제에서는 세 번째 인수(선택 사항)에 1을 전달하여 ordinal 열을 사용하도록 설정합니다.

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

그러면 이 문은 다음 결과 테이블을 반환합니다.

value	서수
Lorem	1
ipsum	2
dolor	3
sit	4
amet.	5

예제

A. CSV(쉼표로 구분된 값) 문자열 분할

쉼표로 구분된 값 목록을 구문 분석하고 비어 있지 않은 토큰을 모두 반환합니다.

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'  
  
SELECT value  
FROM STRING_SPLIT(@tags, ',')  
WHERE RTRIM(value) <> '';

구분 기호 사이에 아무 것도 없을 경우 STRING_SPLIT은 빈 문자열을 반환합니다. RTRIM(value) <> ''조건은 빈 토큰을 제거합니다.

B. 열에서 CSV(쉼표로 구분된 값) 문자열 분할

제품 테이블에는 다음 예제와 같이 쉼표로 구분된 태그 목록이 포함된 열이 있습니다.

ProductId	이름	태그
1	Full-Finger Gloves	clothing,road,touring,bike
2	LL Headset	bike
3	HL Mountain Frame	bike,mountain

다음 쿼리는 각 태그 목록을 변환하고 원래 행과 결합합니다.

SELECT ProductId, Name, value  
FROM Product  
    CROSS APPLY STRING_SPLIT(Tags, ',');

결과 집합은 다음과 같습니다.

ProductId	이름	value
1	Full-Finger Gloves	clothing
1	Full-Finger Gloves	도로
1	Full-Finger Gloves	touring
1	Full-Finger Gloves	bike
2	LL Headset	bike
3	HL Mountain Frame	bike
3	HL Mountain Frame	mountain

참고

출력 순서는 입력 문자열의 하위 문자열 순서와 일치하지 않을 수 있으므로 순서가 달라질 수 있습니다.

C. 값 기준 정렬

사용자는 각 태그별 제품의 수를 표시하고 제품 수 기준으로 정렬한 보고서를 생성하여 제품 수가 2 초과인 태그만 필터링해야 합니다.

SELECT value as tag, COUNT(*) AS [number_of_articles]  
FROM Product  
    CROSS APPLY STRING_SPLIT(Tags, ',')  
GROUP BY value  
HAVING COUNT(*) > 2  
ORDER BY COUNT(*) DESC;

D. 태그 값으로 검색

개발자는 키워드를 기준으로 물품을 찾는 쿼리를 만들어야 합니다. 다음과 같은 쿼리를 사용할 수 있습니다.

단일 태그(의복)가 포함된 제품 찾기:

SELECT ProductId, Name, Tags  
FROM Product  
WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));

지정된 태그 2개(의복 및 도로)가 포함된 제품 찾기:

SELECT ProductId, Name, Tags  
FROM Product  
WHERE EXISTS (SELECT *  
    FROM STRING_SPLIT(Tags, ',')  
    WHERE value IN ('clothing', 'road'));

E. 값 목록 기준으로 행 찾기

개발자는 ID 목록을 기준으로 물품을 찾는 쿼리를 만들어야 합니다. 다음과 같은 쿼리를 사용할 수 있습니다.

SELECT ProductId, Name, Tags  
FROM Product  
JOIN STRING_SPLIT('1,2,3',',')
    ON value = ProductId;

이전 STRING_SPLIT 사용이 일반적인 안티패턴을 대체합니다. 이러한 안티패턴은 TRANSACT-SQL 또는 애플리케이션 계층에서 동적 SQL 문자열을 생성할 수 있습니다. 또는 LIKE 연산자를 사용하여 안티패턴을 구현할 수 있습니다. 다음 SELECT 문 예제를 참조하세요.

SELECT ProductId, Name, Tags  
FROM Product  
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

F. 서수 값으로 행 검색

다음 문은 짝수 인덱스 값을 가진 행을 모두 찾습니다.

SELECT *
FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1)
WHERE ordinal % 2 = 0;

위 문은 다음 테이블을 반환합니다.

value	서수
텍사스	2
워싱턴	4
Colorado	6

G. 서수 값으로 행 정렬

다음 문은 ordinal 열을 기준으로 정렬된 입력 문자열 및 해당 서수 값의 분할 부분 문자열 값을 반환합니다.

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;