MSSQL, SUBSTRING / CHARINDEX / LEN 으로 문자열 나누기

MS SQL Server에서 특정 구분자 패턴으로 저장되어 있는 문자열을 나눌 때 SUBSTRING, CHARINDEX, LEN 함수를 자주 사용합니다. 아마 이 쿼리 패턴을 처음 보시면 복잡해 보일 수도 있지만, 하나씩 분리해 보면 구조가 눈에 들어오실 겁니다. 만약 바로 이해하지 못하더라도 구문을 가져다가 수정해서 바로 쓸 수도 있으니 끝까지 읽어주시면 도움이 되실 겁니다. 

MSSQL 문자열 분할 쿼리

문자열 분할에 사용할 함수인 SUBSTRING, CHARINDEX, LEN 를 각각 간단히 살펴보겠습니다.

SUBSTRING

SUBSTRING 함수는 문자열에서 특정 부분을 추출하는데 사용됩니다.

SUBSTRING(string, start, length)

• string : 부분 문자열을 추출하려는 원본 문자열입니다.
• start : 추출을 시작할 위치입니다. 문자열의 첫 번째 문자는 1로 카운트합니다.
• length : 추출할 부분 문자열의 길이입니다.

 

예를 들어, 'Hello, World!'라는 문자열에서 'World'라는 단어를 추출하려면 다음과 같이 작성할 수 있습니다. 원본 문자열의 8번째 위치에서 시작해서 5개의 문자열을 추출하는 쿼리입니다.

SELECT SUBSTRING('Hello, World!', 8, 5) AS ExtractedString

 

만약 추출하려는 문자열이 항상 일정한 길이의 패턴이면 substring만 써도 분할이 됩니다. 정해진 길이만큼만 분할하면 되니까요. 하지만 실제 환경은 그렇지 않은 경우가 대부분입니다. 대신 . ; | 같은 구분자로 값을 구분하여 저장하는데, 이런 경우에도 문자열을 원하는 대로 자르기 위해 아래의 CHARINDEX가 필요한 것입니다.

CHARINDEX

CHARINDEX 함수는 문자열 내에서 지정한 문자 또는 부분 문자열이 처음으로 나타나는 위치를 반환합니다.

CHARINDEX(expressionToFind, expressionToSearch[, start_location])

• expressionToFind : 찾고자 하는 문자열입니다.
• expressionToSearch : 검색하려는 전체 문자열입니다.
• start_location(optional) : 검색을 시작할 위치입니다. 이 매개변수를 생략하면 문자열의 처음부터 검색합니다.

 

예를 들어, "Hello, World!"라는 문자열에서 "World"라는 단어의 위치를 찾으려면 다음과 같이 작성할 수 있습니다. 이 쿼리의 결과는 8입니다. World라는 단어가 Hello, World!라는 문자열의 8번째 위치에서 시작되기 때문입니다.

SELECT CHARINDEX('World', 'Hello, World!');

 

이번엔 o라는 문자가 몇 번째에 있는지 찾아보겠습니다. 그냥 찾으면 Hello 에서 걸리기 때문에 5를 반환할 겁니다. 그러나 5번째 줄부터 찾으라는 옵션을 주면 그 뒤에 있는 World의 o를 찾아 13을 반환합니다.

SELECT CHARINDEX('o', 'Hello, World!', 5);

 

"아, 문자열의 구분자가 몇 번째 위치에 있는지 확인하기 위해 사용되겠구나"하고 감이 오시죠?

 

LEN

LEN 함수는 문자열의 전체 길이를 반환합니다. 예를 들어, Hello, World!의 전체 길이를 반환하려면 아래와 같이 씁니다.

SELECT LEN('Hello, World!') AS LengthOfName

 

문자열 분할 예시)

이제 위 함수들을 이용해서 구분자로 연결되어 있는 John.Jacob.Smith이라는 영어 이름 문자열을 FirstName, MiddleName, LastName로 구분해 추출해보겠습니다. 아래와 같은 형식으로 추출하면 됩니다. 여기선 변수에 값을 넣는 방식으로 썼지만, 실제로는 테이블 컬럼을 이용하셔도 됩니다.

DECLARE @FullName VARCHAR(100)
SET @FullName = 'John.Jacob.Smith'

SELECT
  SUBSTRING(@FullName, 1, CHARINDEX('.', @FullName) - 1) AS FirstName,
  SUBSTRING(@FullName, CHARINDEX('.', @FullName) + 1, CHARINDEX('.', @FullName, CHARINDEX('.', @FullName) + 1) - CHARINDEX('.', @FullName) - 1) AS MiddleName,
  SUBSTRING(@FullName, CHARINDEX('.', @FullName, CHARINDEX('.', @FullName) + 1) + 1, LEN(@FullName)) AS LastName

• FirstName은 '@FullName' 문자열의 첫 번째 문자부터 첫 번째 '.' 이전까지의 문자열입니다.
• MiddleName은 첫 번째 '.' 이후의 문자부터 두 번째 '.' 이전까지의 문자열입니다.
• LastName은 두 번째 '.' 이후의 모든 문자열입니다.

 

 

쿼리가 복잡해 보이지만 결론적으로 아래의 쿼리 결과와 같습니다. substring에 넣어줄 번호를 만들기 위해 charindex와 len을 사용하는 형태입니다.

SELECT
  SUBSTRING(@FullName, 1, 4) AS FirstName,
  SUBSTRING(@FullName, 6, 5) AS MiddleName,
  SUBSTRING(@FullName, 12, 16) AS LastName

 

CHARINDEX가 어떤 값으로 반환되는지 순서대로 보겠습니다. FirstName은 가장 간단합니다. 첫 '.'이 나오기 전까지의 글자수를 반환하면 됩니다.

 

MiddleName을 구하기 위해서는 첫 '.'이 나온 다음의 순번과 그다음 '.'이 나오기 전까지의 순번을 반환합니다.

 

LastName의 경우 두 번째 '.'이 나온 다음의 순번과 마지막 글자까지 가기 위한 숫자가 필요합니다. 이 경우 정확히는 12, 4가 맞겠지만 뒤에 명시되는 숫자는 초과돼도 문제는 없기 때문에 string(@Fullname, 12, 16)으로도 같은 결과가 나옵니다. 

 

해당 내용을 정확히 이해하지 않더라도 대략적인 느낌은 알게 되었으니 비슷한 상황에 있는 쿼리를 찾아서 사용하시면 됩니다.