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.

Related Tutorial
• Update Query • Adding string to record

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

Now we will try to change the departments exists within the designation field. Before that let us change the department name to Marketing by using this simple update command.

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

In the above table, we can see the department name is changed. Now we have to apply replace query to change the department part of the designation field. The field data saying Manger( Sales) should change to Manager( Marketing) and Manager ( HR) should not change. Now here is the query

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

String Functions
substring_index to get part of a string from a delimiter
substring Part of string from a position
concat: adding a substring to all data present in a field
locate: searching for a matching string inside field data
character_length: length of string present in a field for each record
ucase:lower to upper and upper to lower case conversion functions
left: left part of the string data in a field
replace: replace part of a sting with sub string in a field
length: string length function
reverse: string reverse function
repeat: string repeat number of times
lpad: string padding from left
rpad: string padding from right
like: String pattern matching against record

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?
admin	25-01-2013
thanks

Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email	( not to be displayed)Privacy Policy
1+2=	This is to prevent automatic submission by spammers. Please enter the result of the sum as asked