SQL문에는 「select」 「update」 「insert」 「delete」라고 하는 기본적인 것 외에, substring도 준비되어 있습니다. substring은 직접 데이터를 삽입하거나 지우거나 업데이트하지 않습니다. 대신 문자열의 일부를 잘라내는 기능이 있으며, SQL을 효과적으로 활용하는데 중요한 역할을 하고 있습니다.

substring은 어떤 특징을 가지고 있는가? 이 기사에서 자세히 확인해 봅시다.

substring이란 무엇입니까?

substring은 데이터를 다루는데 유용한 기능을 많이 가지고 있습니다. 이 기사에서는 substring이 가진 세 가지 특징을 설명합니다. 어떤 것인지 확인해 봅시다.

문자열을 잘라내는 함수

substring은 문자열에서 필요한 부분을 잘라내는 함수입니다. 전방 일치나 후방 일치, 특정의 캐릭터 라인이 포함되어 있는지 등의 체크에 활용할 수 있습니다. 구문은 다음과 같습니다.

SUBSTRING (문자열, 시작 위치, 잘라내는 문자 수)

잘라내기 대상이 되는 캐릭터 라인에 가세해, 어디로부터 몇 문자분을 잘라낼까라는 정보가 있으면, 캐릭터 라인의 잘라내기가 가능합니다. 예를 들어, 문자열 “abcdefg”의 시작 부분에서 4자를 반환하는 SQL 문을 아래에 나타냈다.

SELECT SUBSTRING ( 'abcdefg', 1,4);

실행하면 abcd가 결과로 반환됩니다. “문자열” 부분은 단일 따옴표로 묶어야 합니다.

수치에서 잘라내기도 가능

잘라내려는 문자열은 숫자일 수 있습니다. 다음 SQL 문을 살펴 보겠습니다.

SELECT SUBSTRING(1234567890,1,4);

실행하면 1234가 표시됩니다. 숫자의 경우 값을 단일 따옴표로 묶을 필요는 없습니다.

잘라낸 숫자를 계산할 수도 있습니다. 예를 들면 다음과 같은 계산식을 살펴보겠습니다.

SELECT SUBSTRING(1234567890,1,4)+SUBSTRING(1234567890,1,3);

이것은 1234에 123을 더하는 것과 같습니다. 결과는 1357입니다.

사용법은 데이터베이스에 따라 다를 수 있습니다.

substring 문법은 RDBMS 및 버전에 따라 약간 다를 수 있습니다. 예를 들어 Oracle의 경우 다음과 같습니다.

・문자수로 길이를 나타내고 싶은 경우는 「SUBSTR」

・바이트수로 길이를 나타내고 싶은 경우는 「SUBSTRB」

substring에 한정하지 않고, 처음으로 함수를 사용하는 경우는 사전에 매뉴얼등을 참조해, 올바른 사용법을 마스터 해 둡시다.

목적별 · substring의 기본적인 5가지 사용법

substring의 기본적인 사용법은 5가지가 있습니다. 여기에서 MySQL8.0을 사용하여 어떤 SQL 문장을 쓰면 좋은지, 그리고 어떤 결과를 얻을 수 있는지 설명해 봅시다.

이 장에서는 후반부에서 “과일”표를 사용합니다. 표에 포함된 항목과 데이터는 아래와 같습니다.

처음부터 몇 문자를 잘라내고 싶습니다.

처음부터 몇 문자를 잘라내고 싶다면 다음 구문을 사용합시다.

SUBSTRING (문자열 또는 숫자, 1, 잘라내는 문자 수)

예를 들면 「켄코 칸리」라고 하는 어구의 선두로부터 4문자째까지를 잘라내고 싶을 때는, 이하의 SQL문을 실행합니다.

SELECT SUBSTRING ( '켄코 간질', 1,4);

실행하면 「켄코」가 돌려주어집니다. 이 예는 일본어 문자로 시도했지만, 「켄코 간질」의 부분을 「alphabet」등의 영어 단어로 옮겨놓았을 경우에서도, 문제 없게 4문자째까지 잘라낼 수 있습니다.

끝에서 몇 문자를 잘라내고 싶습니다.

업무에 따라서는, 말미로부터의 문자수를 체크하고 싶은 경우도 있습니다. 실현하는 방법은 두 가지로 나뉩니다. 예를 들면 「5문자째로부터 말미까지 잘라내고 싶다」라고 하는 경우는, 이하의 구문을 사용합시다.

SUBSTRING (문자열 FROM 시작 위치)

위의 구문은 잘라내기 시작하는 문자의 위치를 ​​지정하고, 그 뒤의 문자를 모두 잘라내는 방법입니다. 출력되는 문자 수는 원래 문자열에 따라 다릅니다.

여기서 「abcdefg」라고 하는 문자열을 예로, 5문자째로부터 뒤를 잘라내는 SQL문을 확인해 갑시다.

SELECT SUBSTRING ( 'abcdefg'FROM 5);

다섯 번째 문자는 “e”이므로 실행하면 “efg”가 표시됩니다.

한편으로 원래의 문자수에 관계없이, 말미로부터 잘라내는 문자수를 일정하게 하고 싶은 경우는 어떻게 하면 좋을까요. 이 경우 다음 구문을 사용합니다.

SUBSTRING (문자열, - (자르는 문자 수))

마이너스가 붙어 있기 때문에, 절단은 말미로부터 행해집니다. 예를 들어 “abcdefg”라는 문자열에 대해 뒤에 3자를 잘라내는 구문을 확인합시다.

SELECT SUBSTRING('abcdefg', -3);

efg가 결과로 표시됩니다.

중간에 문자열을 잘라내고 싶습니다.

substring에서는 단어나 구절 중간에 포함된 문자열도 잘라낼 수 있습니다. 이 작업은 다음 구문으로 수행할 수 있습니다. 어느 쪽이든, 결과는 동일합니다.

SUBSTRING (문자열 FROM 시작 위치 FOR 문자열의 길이)
SUBSTRING (문자열, 시작 위치, 문자열 길이)

일례로서, 문자열 「abcdefg」의 3문자째와 4문자째를 잘라내는 SQL문을 확인해 갑시다.

SELECT SUBSTRING ( 'abcdefg' FROM 3 FOR 2);
SELECT SUBSTRING ( 'abcdefg', 3,2);

이 SQL문은, 「선두로부터 세어 3문자째로부터 2문자분을 잘라낸다」라고 하는 의미입니다. 결과적으로 세번째와 네번째 문자를 잘라내는 SQL문이 되고, 어느 쪽을 실행했을 경우에서도 cd가 표시됩니다.

시작 위치를 부의 정수로 지정했을 경우는, 말미로부터의 문자수가 됩니다. 다음 SQL 문을 살펴 보겠습니다. 시작 위치의 「3」이 「-3」으로 바뀐 이외는, 조금 전과 같은 SQL문입니다.

SELECT SUBSTRING ( 'abcdefg' FROM -3 FOR 2);

이 SQL문은, 「말미로부터 세어 3문자째로부터 2문자분을 잘라낸다」라고 하는 의미입니다. 따라서 실행하면 ef가 표시됩니다.

지정된 열에 저장된 문자열의 일부를 잘라냅니다.

여기까지 해설한 구문 중, 캐릭터 라인의 부분은 변수에 대신할 수도 있습니다. 이것을 이용해, 테이블의 지정된 열에 포함된 캐릭터 라인의 일부를 잘라내는 것도 가능합니다.

아까에 소개한 「과일」표의 「상품」열에 대해서, 말미가 「특선」으로 끝나는 상품을 검색해 봅시다.

SELECT * FROM 과일 WHERE SUBSTRING(상품, -2, 2)='특선';

이것은 SUBSTRING(문자열, 시작 위치, 문자열의 길이) 구문을 응용한 것입니다. ‘상품’ 열은 ​​변수로 사용하기 때문에 단일 따옴표로 둘러싸여 있지 않습니다. 또 개시 위치에 마이너스를 지정하고 있기 때문에, 말미 2문자에 「특선」이 포함되는 상품이 선택됩니다.

실행한 결과, 말미에 「특선」이 붙는 상품이 표시되었습니다.

SQL문의 여러 위치에서 사용 가능

substring은 SQL 문의 여러 위치에서 사용할 수 있습니다. 지금까지 해설한 SQL문은 대표적인 예입니다. SELECT 문이나 WHERE 절 안에서뿐만 아니라 UPDATE 문 안에서도 사용할 수 있습니다.

이 때문에 「일단 substring으로 잘라낸 캐릭터 라인을 변수에 격납해, 그 변수를 사용해 비교하거나 데이터를 갱신하거나 한다」라고 하는 수고는 필요 없습니다. 잘라내 원래 문자열에서 직접 필요한 부분을 잘라 활용할 수있는 것이 매력입니다.

substring의 효과적인 사용법 3선

substring은 잘 사용함으로써 처리를 효율적으로 행할 수 있습니다. 여기에서는 substring이 도움이 되는 3가지 케이스를 살펴보고 편리함을 확인해 봅시다.

지정된 문자열이 포함된 데이터 추출

지금까지 설명한 대로 substring은 정방향 일치 및 후방 일치와 같이 지정된 문자열을 포함하는 데이터를 추출하는 데 사용할 수 있습니다. 게다가 어구의 중간에 포함되는 캐릭터 라인을 기초로, 필요한 데이터를 추출할 수 있는 경우도 있습니다.

「과일」표의 「상품」열은, 「과일명」의 말미에 「보통」또는 「특선」의 문자가 붙는다고 하는 룰입니다. 이를 이용하여 “과일”이 포함된 데이터를 검색해 봅시다.

SELECT * FROM 과일 WHERE SUBSTRING(상품, -6, 4)='과일';

위의 SQL문에서는, 말미로부터 세어 6번째에 해당하는 문자로부터 3번째에 해당하는 문자까지가 「과일」인 데이터를 표시합니다. 결과는 다음과 같습니다.

키위 과일과 자몽 행이 표시되었습니다.

또 substring에서는 「어구중의 어딘가에, 지정된 캐릭터 라인이 포함되는 데이터」라고 하는 추출 방법에는 대응하고 있지 않습니다.

숫자의 일부를 잘라서 계산

substring에서는 수치의 일부를 잘라내어 계산에 사용할 수도 있습니다. 예를 들면 다음과 같은 경우를 생각해 봅시다.

같은 과일을 5개 사고 싶은 손님이 있다. 이 분은, 5엔이나 10엔이라고 하는 동전을 그다지 갖고 싶지 않다. 가능한 한 낚시가 없도록 지불하고 싶으므로, 5엔이나 10엔이라고 하는 단수가 나오는지 사전에 확인하고 싶다.

이러한 요구사항에는 끝 2자리를 잘라내고 개수를 곱하여 끝수를 확인하는 방법이 유효합니다. 다음 SQL 문으로 실현할 수 있습니다.

SELECT 번호, 상품, SUBSTRING(단가, -2, 2)*5 FROM 과일;

다음 결과가 표시되었습니다.

파인애플이라면 5엔이나 10엔은 불필요합니다. 그 외의 과일을 5개 구입하는 경우는, 10엔 동전이 필요합니다. 그 중에서도 「키위 과일 보통」이나 「파파야 특선」을 5개 사는 경우는, 5엔 동전도 준비하지 않으면 안됩니다.

종류별로 금액을 집계

미리 데이터의 선두나 말미에 종류를 나타내는 정보를 넣고 있으면, substring 함수로 종류별로 집계할 수 있습니다. 단, 「종류를 나타내는 정보」의 문자수가 모두 같은 것이 조건입니다.

「과일」표에는 상품명의 말미에, 「보통」또는 「특선」이라고 하는 등급을 나타내는 어구가 포함되어 있습니다. 같은 등급의 상품을 1개씩 구입했을 경우는 얼마가 되는지, 합계액을 정리하는 SQL문을 이하에 나타냈습니다.

SELECT SUBSTRING(상품, -2, 2) 등급, SUM(단가) 총액 FROM 과일 GROUP BY 등급;

상품명의 말미 2문자를 잘라, 「등급」열로서 명명해 합계액을 산출하는 SQL문입니다. 다음 결과가 표시되었습니다.

substring을 사용할 때의 주의점

substring은 편리하지만 만능이 아닙니다. 또 활용할 때에는 주의해 두고 싶은 포인트도 있습니다. 여기에서는 주의점을 2개 확인해 봅시다.

해당 문자열이 없으면 빈 문자열이 반환됩니다.

데이터에 포함되는 캐릭터 라인의 길이는, 거리의 경우가 많습니다. ‘과일’ 표의 ‘상품’ 열의 경우 최단은 6자이고 최장은 10자입니다. 그렇다면 아래의 SQL문과 같이 9번째 문자의 데이터를 추출한 경우 어떤 결과가 반환될까요?

SELECT 번호, SUBSTRING(상품, 9, 1) FROM 과일;

해당 문자열이 없으면 빈 문자열이 반환됩니다. 결과를 확인하십시오.

3번부터 6번의 4행은 상품명이 9문자 이상이므로 해당 문자가 표시됩니다. 한편, 1번, 2번, 7번, 8번의 4행은 상품명이 8문자 이하이므로, 9문자째를 지정했을 경우는 아무것도 표시되지 않습니다.

날짜 검색과 같은 다른 함수를 사용하는 것이 더 편리 할 수 ​​있습니다.

데이터의 형식에 따라서는, 다른 함수를 사용하는 편이 편리하게 사용할 수 있는 경우도 있습니다. 대표적인 예는 날짜 검색입니다. 데이터 「20220222」로부터 연월일 「2022년 2월 22일」을 꺼내는 것은 substring에서도 가능합니다만, 년, 월, 일의 각각에 대해 1회씩, 합계 3회 사용하지 않으면 안됩니다.

날짜 정보로의 변환은 date_format 함수를 사용하여 원활하게 수행 할 수 있습니다. substring에만 집착하지 않고, 편리한 함수가 있으면 적극적으로 활용하면 좋을 것입니다.

substring의 활용으로 보다 좋은 SQL문을 작성할 수 있다

substring의 활용에 의해, 다양한 데이터의 가공을 실현할 수 있습니다. 잘라낸 데이터의 표시나 변수에의 저장에 그치지 않고, 가공한 데이터를 검색등의 대상열이나 조건에 더해지는 것도 강점입니다.

보다 짧고 효과적인 SQL 문을 만드는 데 substring은 중요한 존재입니다. 다양한 분석에도 도움이 될 것입니다. substring을 배우고 더 나은 SQL 문을 작성하십시오.