SQL REPLACE Function: Find and Replace Text (All Databases)

Overview

REPLACE swaps every occurrence of a substring with a new value. Unlike most string functions, REPLACE has identical syntax in nearly every database.

MySQL

-- REPLACE(string, old, new):
SELECT REPLACE('Hello World', 'World', 'SQL');  -- 'Hello SQL'

-- Update data in place:
UPDATE users
SET email = REPLACE(email, '@old-domain.com', '@new-domain.com')
WHERE email LIKE '%@old-domain.com';

-- Remove characters (replace with empty):
SELECT REPLACE('(555) 123-4567', '-', '');  -- '(555) 1234567'

-- Chain replacements:
SELECT REPLACE(REPLACE(phone, '(', ''), ')', '') AS clean_phone
FROM contacts;

PostgreSQL

-- Same syntax:
SELECT REPLACE('Hello World', 'World', 'SQL');  -- 'Hello SQL'

-- Bulk update:
UPDATE products
SET description = REPLACE(description, 'old term', 'new term');

-- For regex replacement use regexp_replace:
SELECT regexp_replace('abc 123 def', '[0-9]+', 'NUM');  -- 'abc NUM def'

-- Global regex replace:
SELECT regexp_replace('a1b2c3', '[0-9]', '', 'g');  -- 'abc'

SQL Server

-- Same syntax:
SELECT REPLACE('Hello World', 'World', 'SQL');  -- 'Hello SQL'

-- Clean up data:
UPDATE customers
SET phone = REPLACE(REPLACE(REPLACE(phone, '(', ''), ')', ''), '-', '')
WHERE phone LIKE '%(%';

-- STUFF for positional replacement:
SELECT STUFF('Hello World', 7, 5, 'SQL');  -- 'Hello SQL'

Oracle

-- Same syntax:
SELECT REPLACE('Hello World', 'World', 'SQL') FROM DUAL;

-- Remove characters:
SELECT REPLACE('$1,234.56', ',', '') FROM DUAL;  -- '$1234.56'

-- TRANSLATE for character-by-character replacement:
SELECT TRANSLATE('abc123', '123', 'xyz') FROM DUAL;  -- 'abcxyz'

Skip the Syntax Lookup

Instead of memorizing REPLACE syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions