|
SQL > SQL String Functions >
Replace Function
The Replace function in SQL is used to update the content of a string. The function call is REPLACE( ) for MySQL, Oracle, and SQL Server.
REPLACE(str1, str2, str3) finds every occurrence of str2 within str1 and substitutes it with str3 — a straightforward way to clean or transform string data directly in your SQL query.
Syntax
The syntax of the Replace function is:
REPLACE (str1, str2, str3)
In str1, find where str2 occurs, and replace it with str3.
Example
We use the following table for our example.
Table Geography
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
If we apply the following Replace function:
SELECT REPLACE (Region_Name, 'ast', 'astern') REGION1
FROM Geography;
we get the following result:
| REGION1 |
| Eastern |
| Eastern |
| West |
| West |
Frequently Asked Questions
- What does the SQL REPLACE function do?
- REPLACE(str1, str2, str3) searches str1 for all occurrences of str2 and replaces each one with str3. It is supported in MySQL, Oracle, and SQL Server with the same syntax.
- Does REPLACE replace all occurrences or just the first?
- REPLACE replaces all occurrences of str2 within str1, not just the first one. If you need to replace only the first occurrence, consider using a combination of SUBSTRING and LOCATE/CHARINDEX.
- Can I use REPLACE to delete a substring?
- Yes. Pass an empty string as str3:
REPLACE(column_name, 'unwanted', ''). This removes all instances of the specified substring.
- Is the REPLACE function case-sensitive?
- In MySQL it is case-insensitive by default. In Oracle and SQL Server, case sensitivity depends on the collation of the column. Use UPPER() or LOWER() together with REPLACE if you need consistent behavior.
Next: SQL TO_DATE Function
This page was last updated on March 19, 2026.
Copyright © 2026 1keydata.com All Rights Reserved
Privacy Policy About Contact |