SQL REPLACE() Function
The SQL REPLACE() function is used to replace one or more characters (sequence of characters or string) from a string or expression.
The SQL REPLACE() function will replace all available matched strings.
The SQL REPLACE() function is supports or work with character and numeric based columns.
It can be used in any valid SQL SELECT statement as well in SQL where clause.
SQL REPLACE() Syntax
The below syntax is used to replace strings from a given input string or expression.
SELECT REPLACE(string1, find_character_or_string, replace_character_or_string);
The below syntax is used to replace strings from a in a specific column value.
SELECT REPLACE(column_name1, find_character_or_string, replace_character_or_string) FROM table_name;
Parameter Name |
Description |
string or column_name |
Required. The string to replace a sequence of characters or a string with another set of characters or string. |
find_character_or_string |
Required. The sequence of characters or string that will be searched for to replace in string1 or column_name1. |
replace_character_or_string |
Required. The replacement character or string. All occurrences of "find_character_or_string" found within string1 or column_name1 are replaced with "replace_character_or_string". |
Search Keys
- sql replace function
- sql server replace
- sql replace string
- sql replace character
- sql query replace
- sql replace text
- sql search replace
- sql replace value
- sql replace command
- sql find replace
- sql replace example
SQL REPLACE() Example - Using Expression Or Formula
The following SQL SELECT statement replace sequence of characters or a string within a string.
SELECT REPLACE('Sql Tutorial.', '.', '!') AS 'Replaced String';
The result of above query is:
Replaced String |
Sql Tutorial! |
SQL REPLACE() Function More Example
Input Value |
Result |
REPLACE('Database', 'a', '@') |
D@t@b@se |
REPLACE('Sql Tutorial', 'Sql', 'Pl Sql') |
Pl Sql Tutorial |
REPLACE('Sql Query or Command', 'or', '/') |
Sql Query / Command |
REPLACE('Simmanchith.com', 'c', 'C') |
SimmanChith.Com |
Sample Database Table - Employee
ID |
EmpName |
Designation |
Dept |
JoinYear |
Salary |
1 |
Siva Kumar |
SQL Mining |
MySQL |
2013 |
9140 |
2 |
Ramanathan |
Database Security |
Java |
2013 |
16490.3 |
3 |
Chandra |
Sql Team Adminstrator |
Oracle |
2012 |
3260 |
4 |
Azaghu Varshith |
Data Mining |
MS Access |
2013 |
18380 |
5 |
Nirmala |
Database Query Engine |
HTML |
2012 |
5570.7 |
6 |
Hanumanthan |
Cloud Database |
MS Access |
2012 |
12500 |
7 |
Sakunthala |
Project Manager |
PHP |
2015 |
12500 |
8 |
Geetha |
SQL Security |
Java |
2014 |
17330.8 |
9 |
Bala Murugan |
Database Security |
PHP |
2014 |
19640 |
10 |
Padmavathi |
Developer |
PHP |
2012 |
5360 |
11 |
Devi Mai |
SQL Mining |
Java |
2012 |
3260 |
12 |
Harish Karthik |
Big Data |
ASP.Net |
2014 |
6830.5 |
SQL REPLACE() Example
The following SQL statement replace the "EmpName" and "Dept" column from the "Employee" table:
SELECT ID,
EmpName, REPLACE(EmpName, 'a', '@') As 'Replaced - EmpName',
Dept, REPLACE(Dept, 'PHP', 'Personal Home Page') As 'Replaced - Dept'
FROM Employee;
The result of above query is:
ID |
EmpName |
Replaced - EmpName |
Dept |
Replaced - Dept |
1 |
Siva Kumar |
Siv@ Kum@r |
MySQL |
MySQL |
2 |
Ramanathan |
R@m@n@th@n |
Java |
Java |
3 |
Chandra |
Ch@ndr@ |
Oracle |
Oracle |
4 |
Azaghu Varshith |
@z@ghu V@rshith |
MS Access |
MS Access |
5 |
Nirmala |
Nirm@l@ |
HTML |
HTML |
6 |
Hanumanthan |
H@num@nth@n |
MS Access |
MS Access |
7 |
Sakunthala |
S@kunth@l@ |
PHP |
Personal Home Page |
8 |
Geetha |
Geeth@ |
Java |
Java |
9 |
Bala Murugan |
B@l@ Murug@n |
PHP |
Personal Home Page |
10 |
Padmavathi |
P@dm@v@thi |
PHP |
Personal Home Page |
11 |
Devi Mai |
Devi M@i |
Java |
Java |
12 |
Harish Karthik |
H@rish K@rthik |
ASP.Net |
ASP.Net |