WebCodeDoc

SQL REPLACE Function

Quick summary: The SQL REPLACE() function replaces all occurrences of a substring with another substring.

SQL REPLACE Syntax 

REPLACE(string, from_substring, to_substring)
SQL

SQL REPLACE Basic examples 

SELECT REPLACE('hello world', 'world', 'SQL');
SQL
Output:
hello SQL

Replaces part of a string.

SQL REPLACE Real-world usage 

SELECT REPLACE(phone, '-', '') FROM users;
SQL
Output:
Phone numbers without dashes

Normalizes stored data.

SQL REPLACE Edge cases 

SELECT REPLACE('abc', 'x', 'y');
SQL
Output:
abc

Returns original string when no match exists.

SQL REPLACE Common mistakes

Assuming REPLACE is regex-based

REPLACE does not support patterns.

Incorrect
REPLACE(text, '[0-9]', '')
Correct
REGEXP_REPLACE(text, '[0-9]', '')

Use REGEXP_REPLACE for patterns.

SQL REPLACE Frequently Asked Questions

What does REPLACE() do in SQL?

Replaces occurrences of a substring.

Use case of REPLACE()?

Cleaning or transforming strings.

Syntax?

REPLACE(str, from, to).

Common mistake?

Expecting regex support.

Return type?

String.

Handles NULL?

Returns NULL.

Performance?

Fast.

Alternative?

REGEXP_REPLACE.

Used in SELECT?

Yes.

Replaces all occurrences?

Yes.

Used in data cleaning?

Yes.

Best practice?

Avoid heavy transformations in queries.

SQL REPLACE Related SQL Keywords