SQL로 문자열 다듬기 : REPLACE, SUBSTRING, CONCAT

by

SQL로 문자열 다듬기 : 데이터를 조회하다 보면 조회 결과를 바로 사용하기 보다 문자열을 다듬어서 결과로 뽑아야 할 때가 있습니다.

예를 들면 아래와 같은 경우인데요.

  1. 다른 문자열로 수정을 해줘야 하는 경우
  2. 데이터들의 패턴에서 특정 값만 뽑고 싶은 경우 (ex.’도-시-군’ 데이터에서 ‘시’ 값만 뽑아야 하는 경우)
  3. 데이터 포맷을 변경해줘야 하는 경우 (ex. '도', '시' 2개의 칼럼 데이터를 '도 (시)' 포맷으로 하나의 칼럼으로 바꾸고 싶은 경우)

이런 경우 SQL로 어떻게 문자열을 다듬을 수 있는지 정리하였습니다.

문자열 변경하기 : REPLACE

특정 문자열을 찾아서 다른 문자열로 변경하기 위해서는 REPLACE문을 사용합니다.

REPLACE 문의 문법은 다음과 같습니다.

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

'REPLACE' 문은 ‘SELECT'와 사용해서 조회 결과를 변경할 수도 있고 'UPDATE‘와 사용해서 데이터베이스의 내용을 변경할 수도 있습니다.

조회 결과 변경

content 열이 포함된 messages라는 테이블이 있고, 콘텐츠에서 “old” 항목을 모두 “new”로 바꾸고 싶다고 가정해 보겠습니다.

-- replace문 사용법 : replace(바꿀 컬럼, 현재 값, 바꿀 값)
SELECT REPLACE(content, 'old', 'new') AS updated_content
FROM messages;

이 쿼리는 messages 테이블에서 콘텐츠를 선택하여 결과 집합에서 “old”의 모든 인스턴스를 “new”로 바꿉니다.

REPLACE 문은 WHERE 문과 함께 사용하여 특정 대상의 문자열을 변경할 수도 있습니다.

restaurant_name 열이 포함된 food_orders라는 테이블이 있고 restaurant_name 열의 데이터 중 “Blue Ribbon”이라는 문자열이 포함된 경우 “Pink Ribbon”으로 변경하고 싶다고 가정해 보겠습니다.

이 경우, 아래와 같은 쿼리를 사용합니다.

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

데이터베이스 내용 변경

데이터베이스의 실제 데이터를 업데이트하려면 REPLACE와 함께 UPDATE 문을 사용할 수 있습니다.

description 열이 포함된 products라는 테이블이 있고 모든 설명에서 “outdated”을 “updated”으로 바꾸고 싶다고 가정해 보겠습니다.

UPDATE products
SET description = REPLACE(description, 'outdated', 'updated');

원하는 문자열만 남기기 : SUBSTRING

SUBSTRING (혹은 SUBSTR)문은 문자열에서 하위 문자열을 추출하는 데 사용됩니다

기본 문법은 2가지 방식이 있습니다.

  1. 콤마로 구분하는 방식
  2. FROM, FOR를 사용하는 방식
-- 콤마로 구분하여 사용
SUBSTRING(바꿀 칼람, 추출을 시작할 위치, [추출할 길이])

-- FROM, FOR를 사용
SUBSTRING(바꿀 칼럼 FROM 추출을 시작할 위치 FOR [추출할 길이])

문자열 위치는 1부터 시작합니다.

추출할 길이는 옵션입니다. 입력하지 않을 경우, 마지막 문자열 까지를 자릅니다.

주소 데이터가 있는 addr열을 포함하는 food_orders라는 테이블이 있고 여기서 “서울특별시”로 시작하는 데이터를 앞 5글자만 남기고 싶다고 가정해 보겠습니다.

이 경우 아래와 같은 쿼리를 사용합니다.

SELECT addr "원래 주소",
       SUBSTRING(addr, 1, 5) "시"
FROM food_orders
WHERE addr LIKE '서울특별시%';

결과 원래 주소에서 “서울특별시” 문자열만 남는 것을 확인할 수 있습니다.

위의 쿼리는 “서울특별시”로 시작하는 데이터에 한정했지만, addr열에 있는 모든 데이터에서 최상위 주소만 남기고 싶을 때는 어떻게 해야 할까요?

주소는 공백을 기준으로 나누기 때문에 공백을 기준으로 맨 앞의 문자열만 남기면 될 것 같습니다.

이 경우, LOCATE 문을 사용하여 아래와 같은 쿼리를 사용합니다.

우선 LOCATE 문을 아래와 같이 사용해보면 공백의 위치를 찾아주는 것을 확인 할 수 있습니다.

SELECT addr "원래 주소",
       LOCATE(' ', addr) "최상위 행정구역 문자열 위치"
FROM food_orders;

결과를 보면 공백의 위치를 반환하는 것을 확인 할 수 있습니다.

SUBSTRING문의 경우, ‘시작 위치’ 와 ‘길이’ 값을 받으므로 공백의 위치보다 -1의 값을 입력해야 원하는 값들을 얻을 수 있습니다.

따라서 아래와 같은 SUBSTRING, LOCATE 쿼리로 최상위 행정구역의 이름만 따올 수 있게 됩니다.

SELECT addr "원래 주소",
       SUBSTRING(addr, 1, LOCATE(' ', addr) - 1) "도시"
FROM food_orders;

여러 컬럼의 문자를 합치기 : CONCAT

CONCAT 문의 기본 사용법은 아래와 같습니다

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

CONCAT에 붙이고 싶은 값들을 쉼표로 분리해서 적으면 됩니다.

CONCAT으로 붙을 수 있는 값들은 ‘칼럼’, ‘한글’, ‘영어’, ‘숫자’, ‘특수문자’가 있습니다.

만일 위에서 보여준 예시 테이블과 같이 식당 이름, 주소 열을 가진 food_orders 테이블이 있다고 가정했을 때, 주소의 앞 부분과 식당이름을 동시에 보여주고 싶다고 하면 아래와 같은 쿼리를 사용합니다.

SELECT restaurant_name AS "식당",   
       addr AS "주소",
       CONCAT('[', SUBSTRING(addr, 1, LOCATE(' ', addr) - 1), '] ', restaurant_name) AS "지역식당"
FROM food_orders;

Leave a Comment

Content

→ 목차