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

Search Keys

  • sql replace all
  • sql replace string in field
  • search and replace sql
  • sql server replace multiple characters
  • sql replace select
  • oracle str_replace
  • sql query to replace a string in a column
  • replace value sql
  • replace string sql query
  • replace function in sql server
  • string functions in sql