SQL 문자열 가공과 조건문 활용하기 - REPLACE, SUBSTR, CONCAT, IF, CASE

업무 필요한 문자 포맷이 다를 때, SQL로 가공하기 (REPLACE, SUBSTRING, CONCAT)

데이터를 다루다 보면 단순히 숫자 계산만 하는 게 아니라, 문자 데이터를 원하는 형태로 바꿔서 써야 하는 상황이 자주 생긴다. 주소에서 시·도만 따로 뽑아야 할 때, 특정 문자열을 다른 값으로 교체해야 할 때, 여러 값을 하나로 합쳐 보고서에 바로 쓸 수 있게 가공해야 할 때가 그렇다. 오늘은 이런 경우에 유용한 SQL 함수들인 REPLACE, SUBSTRING, CONCAT을 중심으로 실습을 진행했다.

 

1. REPLACE - 잘못된 값 한 번에 바꾸기

데이터를 확인하다 보면 잘못 입력된 값이나 더 이상 쓰이지 않는 값이 남아있을 때가 있다. 이런 값을 하나하나 바꾸기에는 너무 번거롭지만, REPLACE 함수를 사용하면 한 번에 처리할 수 있다.

 

기본 구조

replace(바꿀 컬럼, 현재 값, 바꿀 값)

 

[실습 1] 최근에 상점 이름이 바뀌었지만 과거 데이터에는 옛날 이름으로 저장되어 있는 경우

select restaurant_name "원래 상점명",
       replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'

 

[실습 2] 예전에 '문곡리'라는 지명이 '문가리'로 바뀐 경우

select addr,
       replace(addr, '문곡리', '문가리') "바뀐주소"
from food_orders
where addr like '%문곡리%'  /*쿼리 확인차*/

 

2. SUBSTRING - 필요한 문자만 뽑아오기

주소 전체가 아니라 앞부분의 시·도만 필요할 때는 SUBSTRING (또는 SUBSTR) 함수를 사용한다. 시작 위치와 글자 수를 지정하면 원하는 부분만 잘라낼 수 있다.

 

기본 구조

substr(조회 할 컬럼, 시작 위치, 글자 수)

 

함수명은 substring으로 길게 써도 되고 substr로 짧게 써도 된다.

 

[실습 1] 전체 주소에서 앞부분인 '시도' 부분만 필요한 경우

select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

 

3. CONCAT - 여러 값을 합쳐 새 포맷 만들기

보고서를 작성할 때는 컬럼 하나만 쓰지 않고, 가공해서 보여줘야 할 때가 많다. CONCAT 함수는 여러 값을 붙여 하나의 문자열로 만들어준다.

 

기본 구조

concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, ...)

 

CONCAT은 원하는 만큼 이어붙일 수 있고, 값에는 컬럼뿐 아니라 한글, 영어의 문자열이나 숫자, 기타 특수문자까지 넣을 수 있다.

 

 

[실습 1] 서울시에 있는 음식점은 '[서울] 음식점명' 이라고 수정하는 경우

select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

 

 

※ 보충 학습 - 별칭(alias)와 ORDER BY

실습을 하다 보니, SELECT에서 컬럼에 별칭을 준 뒤 그 별칭을 CONCAT 안에서 써도 되는지 궁금했다. 하지만 쿼리가 순차적으로 처리되기 때문에 SELECT 안에서 지정한 별칭을 같은 SELECT 구문 안에서 바로 참조할 수는 없었다.

 

또 ORDER BY 같은 절에서 별칭을 쓸 경우는 어떨지 궁금해졌다.

 

#1 (별칭 사용, 작은따옴표)
select *, concat('[', substr(gender, 1, 1), ']', name) "새 이름"
from customers
order by '새 이름'  

#2 (쿼리 사용)
select *, concat('[', substr(gender, 1, 1), ']', name) "새 이름"
from customers
order by concat('[', substr(gender, 1, 1), ']', name)

#3 (별칭 사용, 백틱)
select *, concat('[', substr(gender, 1, 1), ']', name) "새 이름"
from customers
order by `새 이름`

 

1번 코드 돌린 결과 (왼쪽), 2번 코드 돌린 결과 (중앙), 3번 코드 돌린 결과 (오른쪽)

 

ORDER BY에서 별칭을 쓸 때 작은따옴표(' ')로 감싸니 원하는 대로 정렬이 되지 않고, 문자 상수로 인식 돼 엉뚱한 결과가 나왔다. ORDER BY, GROUP BY 같은 절에서 별칭을 참조하려면 백틱(``)을 사용해야 한다는 점을 알게되었다. 작은 발견이었지만, 직접 부딪혀가며 문제를 해결하니 SQL 문법의 동작 방식을 더 잘 이해할 수 있었다.

 

 

 

문자 데이터를 바꾸고, GROUP BY 사용하기

문자 함수와 GROUP BY를 같이 쓰면 더 다양한 분석이 가능하다.

 

[실습 1] 서울 지역의 음식 타입별 평균 음식 주문금액 구하기 (출력: '서울', '타입', '평균 금액')

-- 선생님 코드
select substr(addr,1,2) "지역", cuisine_type, avg(price) "평균 금액"
from food_orders
where addr like '%서울%'
group by 1, 2  /* select에 작성한 컬럼 순서대로의 번호를 적어도 괜찮음 */

-- 내 코드
select substr(addr,1,2) "지역", cuisine_type, avg(price) "평균 금액"
from food_orders
where substr(addr,1,2)='서울'
group by cuisine_type

 

 

[실습 2] 이메일 도메인별 고객 수와 평균 연령 구하기

-- 선생님 방법
select substr(email, 10) "도메인", count(1) "고객 수", avg(age) "평균 연령"
from customers
group by 1

 

특정 위치에서 시작해서 마지막 글자까지 가져오고 싶을 때는 SUBSTRING 함수에서 글자 수를 크게 잡거나 아예 생략해주면 된다. 이렇게 하면 시작 위치만 지정해도 그 뒤로 끝까지 문자열을 잘라올 수 있어서, 길이가 일정하지 않은 데이터에도 편하게 활용할 수 있다.

 

 

※  보충학습 - SUBSTRING_INDEX

이번 실습은 기초 문법을 다루는 중이기 때문에 email의 @ 앞부분을 8 글자로 통일해둔 테이블을 사용했다. 하지만 실제 환경에서는 이메일의 아이디 부분 길이가 제각각이라 그대로 적용하기 어렵다. 그럴 때 @ 뒤에 있는 도메인만 깔끔하게 가져올 수 있는 방법이 없을까 궁금해졋고, 찾아보니 SUBSTRING_INDEX() 함수를 사용하면 가능했다.

 

기본 구조

substring_index(컬럼, 구분자, count)

• count > 0: 구분자를 기준으로 왼쪽에서부터 잘라낸다.
• count < 0: 구분자를 기준으로 오른쪽부터 잘라낸다.

예를들어 아래와 같은 쿼리를 사용하면 @ 뒤쪽, 즉, email의 도메인 부분만 가져올 수 있다.

substring_index(email, '@', -1)

 

 

[실습 3] '[지역(시도)] 음식점이름 (음식종류)' 컬럼 만들고, 총 주문건수 구하기

select concat('[', substr(addr, 1, 2), ']', restaurant_name, ' (', cuisine_type, ')') "음식점",
       count(1) "주문 건수"
from food_orders
group by 1

 

이 실습에서 만든 "음식점" 컬럼 안에 들어가는 내용이 많아서 조금 복잡해 보일 수 있다. 이런 경우에는 괄호 앞뒤에 공백을 넣어주면 가독성이 훨씬 좋아진다.

 

 

조건에 따라 포맷을 다르게 변경하기 (IF, CASE)

데이터를 다루다 보면 상황에 따라 결과를 다르게 표현해야 할 때가 있다. 예를 들어 특정 음식 타입은 한글로 바꿔 표시하고 싶거나, 잘못된 이메일 주소만 골라 수정하고 싶을 때처럼 말이다. 이럴 때 사용할 수 있는 것이 IF문과 CASE문이다.

 

1. IF문 - 조건에 따라 다른 방법 적용하기

IF문은 조건문 중에서도 가장 기본적인 문법이다. 원하는 조건이 충족될 때 적용할 값과 그렇지 않을 때 적용할 값을 지정해 줄 수 있다.

 

기본 구조

if(조건, 조건을 충족할 때 값, 충족하지 못할 때 값)

 

[실습 1] 음식 타입 변경하기

음식 타입이 'Korean'일 때 '한식', 그렇지 않을 때는 '기타'로 지정하는 경우:

select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders

 

 

[실습 2] 특정 주소만 변경하기

평택 지역의 '문곡리'만 '문가리'로 수정하고 싶은 경우:

select addr "원래 주소",
       if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'

 

 

[실습 3] 잘못된 이메일 주소 수정하기

gmail에 @ 표시가 빠져 있을 때만 수정해서 도메인만 뽑는 경우:

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

 

이렇게 하면 gmail 주소에 @가 추가되면서 도메인만 정확히 뽑히는 걸 확인할 수 있었다.

그냥 @ 뒤에 도메인만 불러온 경우 (왼쪽), 수정하고 도메인만 뽑은 경우 (오른쪽)

 

앞에서 배운 SUBSTRING_INDEX()를 쓰는 방법도 있다.

select substring_index(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), '@', -1) as domain,
       count(distinct customer_id) cnt_customer, avg(age)
from customers
group by domain

 

 

2. CASE문 - 조건을 여러 가지 지정하기

조건이 두 개 이상으로 늘리면 IF만으로는 코드가 복잡해진다. 이럴 때는 CASE문을 쓰면 여러 조건을 한 번에 정리할 수 있다. 조건별로 지정하기 때문에 if(조건1, 값1, if(조건2, 값2, 값3))와 같이 IF문을 여러번 쓴 효과를 낼 수 있다.

 

 

기본 구조

case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
end

 

[실습 1] 음식 타입을 세분화하기

• 'Korean'일 때 → '한식'
• 'Japanese'나 'Chinese'일 때 → '아시아'
• 나머지 → '기타'

select case when cuisine_type='Korean' then '한식'
            when cuisine_type in ('Japanese', 'Chinese') then '아시아'
            else '기타' end "음식타입", 
       cuisine_type
from food_orders

 

 

이번 실습에서 내가 처음 짠 코드는 아래와 같았다.

select case when cuisine_type='Korean' then '한식'
            when cuisine_type='Japanese' or 'Chinese' then '아시아'
            else '기타' end "음식타입", 
       cuisine_type
from food_orders

 

하지만 실행 결과 오류가 났다. OR 문법이 맞는 것 같았는데 왜 오류가 났던걸까? 알고 보니 WHEN 뒤에는 반드시 논리식이 와야 했다. 즉,

cuisine_type = 'Japanese' or cuisine_type = 'Chinese'

 

처럼  써야 맞는 문법이었다. cuisine_type = 'Japanese' or 'Chinese'는 문자열 리터럴이라 MySQL에서 오류나 예상치 못한 결과를 내는 것이다. 이때 IN을 쓰면 훨씬 간결하게 표현할 수 있었다.

 

 

[실습 2] 조건에 따라 다른 계산 적용하기

주문 수량이 1일 때는 음식 가격 그대로, 2개 이상일 때는 단가(가격/수량)로 계산하기:

select order_id,
       price,
       quantity,
       case when quantity=1 then price
            when quantity>=2 then price/quantity end "음식 단가"
from food_orders

 

 

조건을 활용할 수 있는 경우

조건문은 단순히 값을 바꾸는 것에 그치지 않고 다양한 방식으로 활용할 수 있다.

 

1. 새로운 카테고리 만들기

• 음식 타입 → 한국 음식, 아시아 음식, 미국 음식, 유럽 음식 등
• 고객 분류 → 10대 여성, 10대 남성, 20대 여성, 20대 남성 등 

 

2. 연산식에 조건 지정하기

• ex: 결제 수단별 수수료 계산 (현금, 카드 등)

 

3. 다른 문법과 함께 쓰기

• IF, CASE문 안에 다른 함수를 넣을 수도 있고, 반대로 CONCAT 같은 문법 안에 조건문 넣을 수도 있다.
• ex: CONCAT으로 여러 컬럼을 합칠 때, rating 값이 있으면 포함시키고 없으면 생략하는 방식.

 

 

마무리

이번 실습을 통해 SQL이 단순히 데이터를 불러오는 도구가 아니라, 텍스트를 원하는 형태로 바꾸고 조건에 맞게 분류하는 강력한 도구라는 걸 알게 되었다. 잘못된 문자열을 한 번에 수정하거나, 주소에서 필요한 부분만 뽑아내고, 여러 컬럼을 합쳐 새로운 포맷을 만들 수 있었다. 여기에 IF와 CASE까지 활용하니, 상황에 따라 결과를 다르게 가공하거나 새로운 카테고리를 지정하는 것도 가능했다. 실습 과정에서 작은 문법 차이 하나가 오류를 만들기도 했지만, 그 과정 덕분에 조건문과 문자열 함수의 동작 원리를 더 깊이 이해할 수 있다. 앞으로는 이 함수들과 조건문을 자유롭게 조합해, 실제 업무에서도 바로 활용할 수 있는 데이터로 가공하는 연습을 이어가야겠다.