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;