SQL replace query to substitute part of field data
We know how to change data inside a field by using update command inside a query. Here we will try to learn how to replace part of the data without changing the entire data of the field. The SQL query we design will work like string replacement in any script we use. For this we will use replace command to replace a part of the data inside a field. Let us try to learn this with an example.
In one employee table ( name emp ) we have three fields, name designation and department. There are different designation given based on the name of their department like GM( finance ), Manager (HR), VP(Sales) etc. Now let us say that the department name Sales changed to Marketing in the organization. We can give a single update command in the field department to change the department name to Marketing where ever it is Sales. But the problem is changing the designation of GM( Sales ) to GM ( Marketing), the query should change only for 'Sales' inside department field and should not change their main designation like GM, VP or Manager. It should only change part of the designation saying about the particular function ( or department ).
Here is the table before any changes are applied.
| name |
designation |
department |
| JOhn D |
GM(Sales) |
Sales |
| Nill K |
GM(Finance) |
Finance |
| Poul P |
VP(Sales) |
Sales |
| Kriss A |
VP(HR) |
HR |
| Ricky T |
Manager(Sales) |
Sales |
| Rolls N |
Manager(IT) |
IT |
update emp set department='Marketing' where department='Sales'
The output of this query is shown below
| name |
designation |
department |
| JOhn D |
GM(Sales) |
Marketing |
| Nill K |
GM(Finance) |
Finance |
| Poul P |
VP(Sales) |
Marketing |
| Kriss A |
VP(HR) |
HR |
| Ricky T |
Manager(Sales) |
Marketing |
| Rolls N |
Manager(IT) |
IT |
UPDATE emp set designation=replace(designation, 'Sales', 'Marketing') where department='Marketing'
The output of this query is shown below
| name |
designation |
department |
| JOhn D |
GM(Marketing) |
Marketing |
| Nill K |
GM(Finance) |
Finance |
| Poul P |
VP(Marketing) |
Marketing |
| Kriss A |
VP(HR) |
HR |
| Ricky T |
Manager(Marketing) |
Marketing |
| Rolls N |
Manager(IT) |
IT |
Number of User Comments : 6
| | Surya | 02-11-2009 |
|---|
| Simple and direct solution. Thanks. |
| Rizwan Akber Ali | 07-04-2010 |
|---|
| You are so sweet! Its really very simple way of learning. Thanks! |
| Gouri | 15-12-2010 |
|---|
| What is the difference between update and replace queries in sql server
i mean why we are using replace when we have update?
|
| Mark Piper | 28-01-2011 |
|---|
| Argument data type ntext is invalid for argument 1 of replace function. Can I get around this? |
| JOSE BOLIVAR | 31-01-2014 |
|---|
| thanks very much. direct to point |
|
|
|
|
SQL 