문자 데이터 처리에 필요한 SQL 문자 함수

문자 데이터를 처리하기 위해 사용되는 SQL 함수를 문자 함수라고 부릅니다. 해당 종류의 함수는 꽤나 중요한 SQL 도구 중 하나입니다. 이 글에서는, 문자 데이터를 처리할 수 있는 방법에 관해 몇 가지 살펴보고자 합니다. 문자 데이터를 수정하게 해주는 SQL 함수는 많이 존재합니다. 우리가 SQL을 배우다 보면, 이 함수들이 얼마나 유용했는지를 알게 될 것입니다. 어떤 함수는 불필요한 공백이나 문자를 잘라내주고, 어떤 함수는 문자가 총 몇 개의 알파벳으로 구성되어 있는지 말해줍니다. 이런 함수들은 문자 데이터를 변환시키고 문자 데이터를 다룰 수 있도록 도와줄 뿐만 아니라, 여러분의 쿼리문이 보다 효율적으로 실행될 수 있도록 도와주기도 합니다. 그리고 쿼리문을 보다 이해하기 쉽게 만들어 주기도 하고요. SQL을 다루는 사람이라면, 문자 데이터 조작에 써 먹을 수 있는 문자 함수에 관해 알고 있어야 합니다. 지금부터 대표적으로 자주 사용되는 문자 함수를 소개해보도록 하겠습니다.

 

1. CONCAT 함수

CONCAT(열 이름/문자열, 열 이름/문자열, ... 열 이름/문자열)

CONCAT 함수는 2개 혹은 그 이상의 문자열을 1개의 문자열로 합칠 때 사용합니다. 아래 간단한 예시를 통해 해당 함수가 얼마나 유용하게 사용될 수 있는지 살펴보도록 합시다.

SELECT
 CONCAT('LearnSQL is good', ' and great', ' and fantastic!')
FROM table;

LearnSQL is good and great and fantastic!

CONCAT 함수 괄호 안을 보시면 알 수 있듯이, 저는 지금 총 3개의 문자열을 집어 넣었습니다. (1) 'LearnSQL is good' (2) ' and great' (3) ' and fantastic!'  이렇게 말입니다. 그리고 이 3개의 문자열을 CONCAT 함수를 통해 1개의 문자열로 만들 수 있습니다. 해당 함수는 우리가 데이터베이스 내 저장된 정보를 읽기 좋게 만들 때 사용하면 상당히 유용합니다.

또 다른 예시를 들어보도록 할게요. 예를 들어 우리가 patient 라는 테이블이 있다고 가정해 봅시다. 해당 테이블에는 환자의 숫자 아이디, 이름(name), 입원 날짜(date), 환자가 앓고 있는 병(illness)에 관한 정보가 저장되어 있습니다. 우리는 이런 환자의 데이터를 읽기 좋게 만들어서 보여주고 싶습니다. 가장 좋은 방법은 아무래도 해당 데이터를 활용해 우리가 이해할 수 있는 문장으로 나타내는 것이 아닐까 싶네요.

SELECT
 CONCAT(name, ' was admitted to St. Ann's Hospital on ', date, ' with ', illness)
FROM patient
WHERE patient_id = 447;

John Doe was admitted to St. Ann's Hospital on 2021-01-14 with flu.

대부분의 관계형 데이터베이스에서는 CONCAT 함수를 II 라는 표현(다른 말로, 연산자)으로 대체해 사용할 수 있습니다. 하지만 모든 데이터베이스가 그런 것은 아니니 유념해주세요. SQL Server 의 경우는 + 라는 연산자로 대체해 사용할 수 있습니다. 또한 대부분의 관계형 데이터베이스에서 CONCAT 함수를 사용해 원하는 만큼의 문자열을 연결할 수 있지만, Redshift 의 경우는 CONCAT 함수는 2개의 문자열만 합칠 수 있어서, 이 경우에는 CONCAT 함수 대신 II 연산자를 사용하여 3개 이상의 문자열을 합쳐줄 수 있습니다. 

 

2. REPLACE 함수

REPLACE(열 이름/문자열, 기존 문자열, 수정할 문자열)

해당 함수는 기존의 문자열을 다른 문자열로 대체하고 싶을 때 사용합니다. 만약 수정할 문자열이 NULL 값이라면, 이는 값이 존재하지 않는다는 의미이기 때문에 기존 문자열은 삭제된 상태로 반환됩니다. 이번에도 2개의 예시를 살펴보도록 하겠습니다.  

SELECT
 REPLACE( 'LearnSQL is good!', 'good', 'great')
FROM table;

LearnSQL is great!

바로 알 수 있다시피 REPLACE 함수를 통해 문자열 'good' 을 'great' 으로 대체했습니다. 원래 문장은 'LearnSQL is good!' 이었지만 해당 함수를 통해 바뀐 문장은 'LearnSQL is great!' 이네요.

이번에는 좀더 비즈니스 상황스러운 예시를 들어보도록 하겠습니다. 예를 들어 registry 라는 테이블이 있다고 가정해봅시다. 거기에는 직원의 성과 이름과 같은 정보가 저장되어 있습니다. 예를 들어 Jannet 이라는 이름을 가진 직원이 결혼을 해서 성을 바꾸었다고 해봅시다. 이럴 때 REPLACE 함수를 사용하면 쉽게 직원의 정보를 업데이트 할 수 있습니다.

UPDATE registry
SET name = REPLACE(name, 'Kowalski', 'Novak')
WHERE name LIKE 'Jannet%';

자, REPLACE 함수 덕에 손쉽게 Jannet Kowalski 를 Jannet Novak 으로 업데이트 할 수 있었습니다.

 

3. SUBSTR 함수

SUBSTR(열 이름/문자열, 문자 추출을 시작할 위치, 추출할 문자의 개수)

SUBSTR 함수는 문자열에서 일정 부분의 문자열을 추출해 내는 함수입니다. 괄호 안의 첫 번째에는 우리가 조작하고자 하는 문자열이나 해당 문자열이 저장되어 있는 열 이름을 적어주면 됩니다. 그러고 난 후, 어디서부터 문자를 추출할 것인지 그리고 그곳을 기준으로 총 몇 개의 알파벳을 추출할 것인지 적습니다. 아래 예시에서의 SUBSTR 함수를 해석해보면 LearnSQL 이라는 문자열을 가지고 6번째부터 총 3개의 문자를 추출하라고 하네요. LearnSQL 내의 문자열에서 6번째는 S 이고 S 를 시작으로 총 3개의 알파벳을 추출하라고 하니 SQL 이라는 결과값을 얻었습니다.

SELECT
 SUBSTR('LearnSQL', 6, 3)
FROM table;

SQL

해당 함수는 긴 문자열에서 몇 개의 문자를 없애고 그 안에 다른 문자열을 넣을 때 주로 사용되고는 합니다. 예를 들어보도록 하죠. product 라는 테이블 안에는 상품 아이디가 저장되어 있다고 가정해 봅시다. 이 상품 아이디들은 상품의 카테고리를 지칭하는 문자열과 총 몇 번째의 상품인지 말해주는 숫자열로 구성되어 있습니다. 아래는 해당 상품 아이디 값입니다.

id

AA91

AA55

BG66

WP21

자, 여기서 우리는 상품 아이디의 문자열과 숫자열 사이에 언더바( _ )를 넣고 싶습니다. 어떻게 하면 될까요? 바로 CONCAT 함수와 SUBSTR 함수를 함께 사용하면 됩니다. 한 번 보시죠.

UPDATE products
SET id = CONCAT(SUBSTR(id, 1, 2), '_', SUBSTR(id, 3));

id

AA_91

AA_55

BG_66

WP_21

위의 쿼리문을 보면 UPDATE 문은 product 테이블 내의 id 값을 업데이트 했고, CONCAT 함수는 SUBSTR 함수로 생겨난 2개의 문자열을 언더바( _ )로 합쳐주었습니다. 우리가 유념해서 살펴봐야 할 것은 2번째 SUBSTR 함수의 3번째 매개변수(추출할 문자의 개수)를 명시하지 않았다는 점입니다. 이 말은 곧, 2번째 매개변수(문자 추출을 시작할 위치)를 시작점으로 하여 뒤에 있는 나머지 문자를 전부 다 반환하라는 말입니다. 

모든 관계형 데이터베이스가 똑같은 이름의 함수를 갖고 있지는 않습니다. SQL Server 에서 SUBSTR 함수는 SUBSTRING 함수라고 정의됩니다. 물론 해당 함수가 하는 역할이나 문법은 동일합니다. 다만, 이름이 다를 뿐이니 주의해 주세요. 

 

4. TRIM 함수

TRIM( [ [ LEADING I TRAILING I BOTH ] 없앨 문자 FROM ] 기존 문자열) 

보통 TRIM 함수는 공백을 제거할 때 사용합니다. TRIM은 '다듬다'라는 뜻으로, 문자를 다듬어준다고 생각하시면 됩니다. 보통은 공백을 제거할 때 사용하지만, 일반 문자 및 숫자, 특수 문자를 제거할 때도 사용합니다. TRIM 함수를 통해 왼쪽 시작 부분부터 문자를 다듬을 수도 있고, 끝 부분부터 문자를 다듬을 수도 있고, 양쪽 모두를 다듬을 수도 있습니다. 해당 함수는 매개 변수가 많아서 조금 헷갈릴 수도 있습니다. 가장 처음에 왼쪽 혹은 오른쪽부터 문자를 없앨 것인지 명시해 줘야 합니다. 명시하지 않으면 해당 함수는 양쪽 모두로부터 지정한 문자열을 없앨 것입니다. 그런 후, 어떤 문자열을 없앨 것인지 명시합니다. 만약 어떤 문자열을 없앨 것인지 명시하지 않으면, 해당 함수는 공백만 없앨 것입니다. 그러고 나서 마지막으로 기존 문자열을 적어줍니다.

실제로 어떤 식으로 해당 함수가 작동하는지 보시죠.

이건 양쪽 부분 모두로부터 공백을 없애는 쿼리문입니다.

SELECT
 TRIM('       SQL2021      ')
FROM table;

SQL2021

SELECT
 TRIM (' ' FROM '      SQL2021       ')
FROM table;

SQL2021

이건 오른쪽 (trailing) 부분부터 공백을 없애는 쿼리문입니다.

SELECT
 TRIM(TRAILING '2021' FROM 'SQL2021')
FROM table;

SQL

이건 양쪽 부분 모두로부터 숫자열을 없애는 쿼리문입니다.

SELECT
 TRIM(BOTH '20' FROM '2021LearnSQL20')
FROM table;

21LearnSQL

안타깝게도, TRIM 함수는 데이터베이스마다 서로 다른 문법 구조를 지닙니다. SQL Server 의 TRIM 함수는 오직 기존 문자열 매개변수만 인식할 수 있어서 양쪽의 공백란을 없앨 때만 사용할 수 있습니다. 만약 해당 데이터베이스에서 왼쪽 혹은 오른쪽의 공백란을 없애기 위해서는 LTRIM 함수 혹은 RTRIM 함수를 사용해야 합니다.

 

5. LEFT 함수, RIGHT 함수

LEFT(문자열, 왼쪽부터 반환해 낼 알파벳 개수)

RIGHT(문자열, 오른쪽부터 반환해 낼 알파벳 개수)

LEFT 함수와 RIGHT 함수는 문자 함수 중에서 상당히 유명한 함수에 속합니다. LEFT 함수의 경우 왼쪽부터 시작해서 특정 숫자만큼의 문자열을 반환하고, RIGHT 함수의 경우 오른쪽부터 시작해서 특정 숫자만큼 문자열을 반환해 냅니다. 아래 예시를 보면 쉽게 이해하실 수 있으실 겁니다.

SELECT
 LEFT('Hello World', 5) AS left
 RIGHT('Hello World', 5) AS left
FROM table;

left	right
Hello	World

 

6. LOWER 함수, UPPER 함수

LOWER(문자열)

UPPER(문자열)

LOWER 함수와 UPPER 함수도 문자 함수 중에서 잘 알려진 함수들입니다. LOWER 함수의 경우 모든 문자열을 소문자로, UPPER 함수의 경우 모든 문자열을 대문자로 변환합니다. 

SELECT
 LOWER('Hello World') AS lower,
 UPPER('Hello World') AS upper
FROM table;

lower	upper
hello world	HELLO WORLD

 

이외에도 문자 함수의 종류는 다양합니다. 문자 함수를 아는 것은 데이터 전처리에도 도움이 될 뿐만 아니라, 리포트를 작성할 때도 용도에 맞게 해당 데이터를 다루기 쉬운 형식으로 만들어야 하는 경우가 생기기 때문에 꼭 알아두는 것이 좋습니다.

 

원문 : 5 SQL Functions for Manipulating Strings

원문 : Overview of SQL String Functions