---
title: "REPLACE in an UPDATE statement"  
description: "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"  
author: "AVADHESH PATEL"  
published: 2012-09-05  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/352/replace-in-an-update-statement  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# REPLACE in an UPDATE statement

This article covers using the REPLACE function to selectively replace [text inside](https://www.mindstick.com/forum/23039/to-scroll-a-text-inside-picture-box) a [string in SQL](https://www.mindstick.com/interview/33826/how-to-find-the-length-of-a-string-in-sql-server) Server. The REPLACE function is easy to use and very handy with an UPDATE statment.\

Replace searches for certain [characters](https://answers.mindstick.com/qa/42112/who-is-the-originator-of-avengers-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](https://answers.mindstick.com/qa/33541/list-top-10-tiger-reserves-in-india-with-maximum-tiger-population) is 1.22 billion

I replaced 15 [character](https://www.mindstick.com/articles/23551/an-investigate-distinctive-seafood-restaurant-for-your-image-stamp-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](https://answers.mindstick.com/qa/32633/what-is-the-origin-of-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](https://www.mindstick.com/interview/1909/when-do-you-use-where-clause-and-when-do-you-use-having-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](https://www.mindstick.com/interview/34374/encode-and-decode-string-function-in-python) 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](https://www.mindstick.com/forum/159366/how-to-test-regular-expressions-in-sql-without-using-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;

---

Original Source: https://www.mindstick.com/blog/352/replace-in-an-update-statement

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.