This article covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and very handy with an UPDATE statment.
Replace searches for certain characters in a string and replaces them with other characters. So this statement:
SELECT REPLACE('India Population 2012 is 1.00 billion','1.00','1.22')
Will return
India Population 1012 is 1.22 billion
REPLACE searches the first string for any occurrence of the second string and replaces it with the third string. You can also do replacements of different sizes. For example
SELECT REPLACE('India Population 2012 is 1.00 billion','Population 2012','current population')
Will return
India current population is 1.22 billion
I replaced 15 character strings with 18 character string with no problem.
If the string isn't found, no changes will be made.
SELECT REPLACE('India Population 2012 is 1.00 billion','1.25','1.22')
Returns exactly what we started with which is
India current population is 1.00 billion
You can use REPLACE in an UPDATE statement.
UPDATE DBO.EMPLOYEE SET NAME=REPLACE(NAME,'XYZ','MR. XYZ')
There were two authors that had "Allahabad City" in the CITY field. Now that field holds “Kanpur City" for those two authors. The CITY field is unchanged for all the other authors.
A more common approach is to use this in conjunction with a WHERE clause like this:
UPDATE DBO.EMPLOYEE SET CITY=REPLACE(CITY,'ALLAHABD','KANPUR') WHERE CITY LIKE 'ALLAHABAD%'
This only affects the rows that start with ‘ALLAHABAD’.
Note:
The replace is a string function used in SQL. The UPDATE is a command telling the database what to do. So for example if you want to perform changes in a database table you would use:
UPDATE table SET field = newvalue;
On the other hand REPLACE is a function which can be used to replace one string with another and return the new string.
SELECT REPLACE (field, oldstring, newstring) FROM table;
Anonymous User
27-Apr-2019Thanks for sharing.