Keeping string data accurate is a routine but critical part of working with databases. SQL’s REPLACE() function lets you update one string with another directly within your query. Whether it’s correcting typos, rebranding statuses, or rolling over a year reference, REPLACE makes it easy and efficient.
Examples of SQL REPLACE in Action
Standard syntax:
REPLACE(column, 'old_string', 'new_string')
Change a year value
UPDATE products
SET description = REPLACE(description, '2023', '2024')
WHERE description LIKE '%2023%';
Update product status
UPDATE products
SET status = REPLACE(status, 'On Sale', 'Discounted')
WHERE status = 'On Sale';
Case-insensitive replacement
UPDATE employees
SET job_title = REPLACE(LOWER(job_title), 'technician', 'engineer')
WHERE LOWER(job_title) LIKE '%technician%';
This ensures consistency regardless of input case.
FAQ
Can REPLACE work on columns with NULLs?
No. If the value is NULL, REPLACE skips it. You can use COALESCE() to default the value and avoid this issue.
Is this function available in all RDBMS?
Yes. Major databases support it, though PostgreSQL users may also explore REGEXP_REPLACE for pattern replacements.
How do I replace two different words at once?
Use nested functions:
REPLACE(REPLACE(column, 'old1', 'new1'), 'old2', 'new2')
Is it case-sensitive?
Yes. You'll need to convert to lowercase or uppercase manually if you want to ensure a match regardless of case.
Conclusion
SQL REPLACE() is a simple but highly effective way to keep text data accurate and clean. From handling simple find-and-replace operations to transforming columns during updates, it’s a tool every SQL developer should know.
To explore its nuances across platforms like PostgreSQL, MySQL, and SQL Server:
Read the full REPLACE guide: SQL REPLACE Function: A Comprehensive Guide.
Top comments (0)