SQL REPLACE() 함수 사용법

SQL REPLACE() 함수로 데이터베이스에서 하위 문자열을 찾고 치환하는 방법을 배워 보세요. 문법, 대소문자 처리, 문자 제거, NULL 처리, 성능 팁을 다룹니다.

업데이트됨 2026년 6월 3일 · 7분 읽다

SQL REPLACE() 함수는 문자열의 일부를 지정한 다른 하위 문자열로 바꿀 수 있어 텍스트 처리에 유용합니다. 대부분의 경우 SQL REPLACE() 함수는 주어진 데이터셋의 문자열 내 모든 하위 문자열 발생을 수정하는 데 사용됩니다.

텍스트 처리는 데이터를 효율적으로 분석할 수 있도록 정리하고 변환하는 데 도움이 되므로 SQL에서 중요합니다. 이 튜토리얼에서는 사용 방법을 보여 드리겠습니다.

데이터 과학자 또는 데이터 엔지니어를 목표로 한다면, 데이터 변환에서 SQL 함수의 기초를 이해하기 위해 DataCamp의 Introduction to SQL 과정을 수강해 보세요. 또한 주제를 더 깊이 탐구하는 동안 기초 개념을 복습할 수 있도록 SQL 문자열 함수 가이드도 확인하시기 바랍니다.

요약

REPLACE(string, old_substring, new_substring)는 한 번의 호출로 하위 문자열의 모든 발생을 바꿉니다
여러 하위 문자열을 바꾸려면 체이닝하세요: REPLACE(REPLACE(col, 'a', 'b'), 'c', 'd')
문자를 제거하려면 빈 문자열을 전달하세요: REPLACE(phone, '-', '')
인수 중 하나라도 NULL이면, 해당 행에 대해 함수는 NULL을 반환합니다
대소문자 구분은 데이터베이스와 정렬 규칙에 따라 다릅니다—SQL Server와 MySQL은 기본적으로 대소문자 구분 없음, PostgreSQL은 기본적으로 대소문자 구분

SQL REPLACE() 함수 이해하기

SQL의 REPLACE() 함수는 열에서 지정한 하위 문자열 또는 문자열을 찾아 다른 문자열로 바꿉니다. 아래 예시는 REPLACE() 함수의 기본 사용법을 보여 줍니다. 함수의 인수는 다음과 같습니다.

string: 치환이 이루어질 원본 문자열.
old_substring: 대체될 하위 문자열.
new_substring: 기존 하위 문자열을 대체할 하위 문자열.

REPLACE(string, old_substring, new_substring)

REPLACE() 함수를 사용해 테이블의 문자열을 변경할 때, 쿼리는 다음 구문을 갖습니다. 여기서:

column_name: 하위 문자열 발생을 검색할 열.
old_substring: 대체될 하위 문자열.
new_substring: 기존 하위 문자열을 대체할 하위 문자열.

-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT 
    REPLACE(column_name, 'old_substring', 'new_substring') AS updated_column
FROM 
    Products;

REPLACE() 예시

SQL REPLACE() 함수는 지정한 하위 문자열 또는 문자열로 모든 문자열 발생을 변경하거나 대체합니다. 아래 예시에서 SQL의 REPLACE() 함수는 description 열에서 old 값을 new로 바꾸었습니다. 쿼리는 각 행을 실행하여 기존 문자열을 새 문자열로 대체합니다.

-- Select the description column from the products table,
-- replacing occurrences of 'old' with 'new'
SELECT 
    REPLACE(description, 'old', 'new') AS updated_description
FROM 
    Products;

참고: MySQL, PostgreSQL, Oracle, SQL Server 데이터베이스는 모두 REPLACE() 함수를 지원합니다.

SQL에서 REPLACE()의 활용 사례

SQL REPLACE() 함수는 텍스트 처리와 데이터 변환에서 다양한 중요 활용 사례가 있습니다. 다음과 같습니다.

기본 치환

SQL REPLACE() 함수로 기본 치환을 수행할 수 있습니다. 다음과 같은 테이블이 SQL Server 데이터베이스에 있다고 가정해 봅시다.

Products 테이블 예시. 이미지: 작성자.

다음 SQL 쿼리를 사용하면 REPLACE() 함수로 old 상태를 new로 변경할 수 있습니다.

SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

결과 테이블은 아래와 같이 표시됩니다.

SQL REPLACE()로 업데이트된 테이블. 이미지: 작성자.

여러 하위 문자열 치환

여러 하위 문자열을 바꾸려면 REPLACE() 함수를 여러 번 체이닝하세요. 여기서는 old를 outdated로, new를 fresh로 업데이트합니다.

-- Chain multiple REPLACE() functions to replace 'old' with 'outdated' and 'new' with 'fresh'
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(REPLACE(status, 'old', 'outdated'), 'new', 'fresh') AS updated_status
FROM Products;

결과 테이블은 아래와 같습니다.

여러 하위 문자열로 업데이트된 테이블. 이미지: 작성자.

문자 제거

문자를 완전히 제거하려면 세 번째 인수로 빈 문자열을 전달하세요. 열에서 문장 부호, 불필요한 공백, 서식 문자를 제거하는 가장 깔끔한 방법입니다.

-- Remove dashes from phone numbers
SELECT
    customer_id,
    REPLACE(phone_number, '-', '') AS clean_phone
FROM Customers;

예를 들어, '555-867-5309'는 '5558675309'가 됩니다. 동일한 기법으로 가져온 데이터의 특수 문자, 인코딩 아티팩트, 원치 않는 공백을 제거할 수 있습니다.

대소문자 구분 처리

REPLACE()의 대소문자 구분은 데이터베이스와 정렬 규칙에 따라 다릅니다. PostgreSQL은 기본적으로 대소문자 구분이 있으므로 REPLACE(col, 'Old', 'New')는 'old'와 일치하지 않습니다. SQL Server와 MySQL은 열 또는 데이터베이스의 정렬 규칙을 따르며—대부분의 기본 설치는 대소문자 구분이 없는 정렬을 사용하므로 REPLACE()는 대소문자와 관계없이 일치합니다.

대소문자를 구분하지 않고 값을 바꾸려면 치환 전에 UPPER() 또는 LOWER() 함수를 사용해 텍스트를 변환하세요. 이 기법은 값을 일관되게 유지합니다.

-- Replace 'OLD' with 'OUTDATED', handling case sensitivity by converting to upper case
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(UPPER(status), 'OLD', 'OUTDATED') AS updated_status
FROM Products;

REPLACE()와 UPPER() 함수를 사용해 업데이트된 테이블. 이미지: 작성자.

-- Replace 'old' with 'outdated', handling case sensitivity by converting to lowercase
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(LOWER(status), 'old', 'outdated') AS updated_status
FROM Products;

SQL REPLACE()와 LOWER() 함수를 사용해 업데이트된 테이블. 이미지: 작성자.

고급 기법

이 기법들은 REPLACE()를 다른 SQL 함수와 결합해 단순한 찾기-치환을 넘어서는 대체 작업을 수행합니다.

동적 값으로 치환

REPLACE() 함수는 다른 SQL 함수와 조합하여 동적 치환을 수행할 수 있습니다. 예를 들어, REPLACE() 함수를 사용해 다른 열의 값을 하위 문자열 대체에 활용할 수 있습니다. 아래 예시에서는 REPLACE() 함수가 각 오래된 제품 텍스트를 product_name의 실제 값으로 바꿉니다.

-- Replace 'old' with the value from product_name column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', product_name) AS updated_status
FROM Products;

REPLACE() 함수와 동적 값으로 업데이트된 테이블. 이미지: 작성자.

UPDATE 문에서 REPLACE() 사용

SQL REPLACE() 함수는 UPDATE 문 내부에서 작동하여 하나 이상의 행에 걸쳐 열 값을 수정합니다. 아래 쿼리는 status 열의 old 값을 outdated로 업데이트합니다.

-- Update the status column to replace 'old' with 'outdated'
UPDATE Products
SET status = REPLACE(status, 'old', 'outdated')
WHERE status LIKE '%old%';

조인으로 테이블을 업데이트하는 등 더 복잡한 시나리오의 경우, SQL UPDATE with JOIN 튜토리얼을 참고하세요.

REPLACE()와 다른 문자열 함수 결합

고급 기법 중 하나는 문자열 값을 치환할 때 CHARINDEX()나 SUBSTRING() 같은 다른 SQL 문자열 함수와 REPLACE()를 결합하는 것입니다.

-- Replace part of the product name starting from the first space with 'Updated'
SELECT 
    product_id,
    product_name,
    price,
    -- Extract the substring of product_name starting from the first space to the end
    REPLACE(product_name, SUBSTRING(product_name, CHARINDEX(' ', product_name), LEN(product_name)), ' Updated') AS updated_product_name
FROM Products;

위 쿼리의 결과 테이블은 아래와 같습니다.

REPLACE()와 다른 문자열 함수로 업데이트된 테이블. 이미지: 작성자.

NULL을 0으로 치환

COALESCE() 함수를 사용해 REPLACE()를 호출하기 전에 NULL 값을 대체값으로 치환할 수 있습니다. 아래와 같습니다.

-- Replace NULL with 'new' in the status column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(COALESCE(status, 'NULL'), 'NULL', 0) AS updated_status
FROM Products;

주의: REPLACE()에 전달되는 인수 중 하나라도 NULL이면, 다른 인수가 유효하더라도 함수는 해당 행에 대해 NULL을 반환합니다. 널 허용 열은 REPLACE()를 호출하기 전에 COALESCE()로 감싸세요. 나중이 아닙니다.

SQL REPLACE() 함수의 실무 활용

REPLACE()가 실무 쿼리에서 제 역할을 하는 대표적인 시나리오는 다음과 같습니다.

데이터 정제: 데이터셋의 문자열에서 원치 않는 문자나 공백을 제거하고 대체하는 데 REPLACE() 함수가 유용합니다.
데이터 변환: 값을 다른 형식으로 바꾸거나 오래된 값을 최신 값으로 교체하는 데이터 변환에 REPLACE() 함수가 사용됩니다.
사용자 입력 처리: 특정 데이터베이스에서 허용되는 표준에 맞도록 사용자 입력 데이터를 조정·변환하는 데 REPLACE() 함수가 중요합니다. 이 기법은 데이터베이스의 일관성을 보장합니다.
텍스트 표준화: 소문자/대문자 약어와 같이 문자열 열의 값을 표준화해 일관성을 확보합니다.
조건부 치환: 특정 규칙에 따라 값을 교체하는 등 간편한 데이터베이스 관리에 REPLACE() 함수가 유용합니다.

데이터베이스별 REPLACE() 구현

MySQL, PostgreSQL, Oracle, SQL Server 데이터베이스는 모두 SQL의 REPLACE() 함수를 지원하며, 이들 간 구문은 유사합니다.

-- Replace 'old' with 'new' in the status column
SELECT 
    product_id,
    product_name,
    price,
    REPLACE(status, 'old', 'new') AS updated_status
FROM Products;

REPLACE() 성능 고려 사항 및 모범 사례

REPLACE() 함수를 사용할 때 다음의 성능 고려 사항과 모범 사례를 유념하세요.

쿼리 성능에 미치는 영향

REPLACE() 함수는 특히 대규모 데이터셋을 처리할 때 쿼리 성능에 영향을 줄 수 있습니다. 각 행에 대해 테이블을 업데이트하므로 쿼리 실행에 시간이 걸릴 수 있기 때문입니다.

인덱싱 고려 사항

WHERE 절이나 UPDATE에서 REPLACE()를 실행하기 전에 검색 대상 열에 인덱스를 설정하세요. 인덱스가 없으면 일치 항목을 찾기 위해 데이터베이스가 모든 행을 스캔해야 하며—대형 테이블에서 특히 비용이 큽니다.

성능 병목 모니터링과 해결

쿼리 성능과 관련된 문제를 해결하려면 다음 모범 사례를 고려하세요.

데이터베이스 프로파일링 도구를 사용해 쿼리 성능을 모니터링하고, 대규모 데이터셋 처리 시 최적화하세요.
REPLACE()로 문자열 값을 수정할 때 인덱스가 일치하도록 정기적으로 유지 관리하세요.
대규모 데이터셋을 처리할 때 배치 처리를 도입해 쿼리 실행 시간을 줄이세요.

데이터 변환을 위한 다른 고급 SQL 함수를 배우려면 DataCamp의 Learn SQL 과정을 추천합니다. Reporting in SQL 과정에서는 데이터베이스 최적화 주제도 다루어 성능 최적화 기법을 학습할 수 있습니다.

마무리

REPLACE() 함수는 대부분의 SQL 데이터베이스에서 텍스트 치환 요구를 폭넓게 충족합니다. NULL 동작—인수 중 하나라도 NULL이면 NULL 반환—을 염두에 두고, 대소문자 구분이 중요할 때는 데이터베이스의 정렬 설정을 확인하세요.

SQL의 REPLACE() 함수에 대해 더 알아보려면 DataCamp의 Intermediate SQL 및 SQL Fundamentals 과정을 수강해 보세요. 또한 데이터 변환에서 SQL의 활용을 더 깊이 이해하고 커리어를 시작하는 데 도움이 되는 Associate Data Analyst in SQL 커리어 트랙을 추천합니다. 마지막으로, 구직 인터뷰에서 돋보이고 SQL로 데이터 분석 역량을 입증할 수 있도록 DataCamp의 SQL Associate Certification 취득을 권장합니다.

마지막으로, 데이터 분석에 사용되는 다양한 SQL 함수에 대해 더 배우고 싶다면 다음 튜토리얼도 확인해 보세요.