SQL replace query to substitute part of field data

SELECT REPLACE( name, 'John','Alex')   FROM `student`
Here in student table name column, John name will be replaced by Alex

SELECT REPLACE('main string','search string', 'replace string')
We can apply search and replace on a table column or string by using REPLACE.

REPLACE() is Case sensitive

SELECT REPLACE('hello','H','k') AS L1; # Output hello
To replace lower case chars here we have to use LOWER()
SELECT REPLACE('hello',LOWER('H'),'k') AS L1; # Output kello 
Similarly to replace upper case chars.
SELECT REPLACE('Hello',UPPER('h'),'k') AS L1; # Output kello
This will replace both Lower case and Upper case char ( A and a )

Difference between UPDATE and REPLACE

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
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

Remove commas , from string to convert to Integer column

In some tables we will have , ( comas ) as number formats and the column is used as text or varchar column. To apply any math calculation or to list in order by we have to convert the text or varchar column to integer column. Before converting we have to remove the , ( commas ) from the data and then apply changes. Here is the query to remove commas from the data.

Here p_view is the column having commas inside the data and y21_11 is the table name.
UPDATE  y21_11 SET p_view=REPLACE(p_view,',','')
After removing the commas the column is changed to int type.
ALTER TABLE `y21_11` CHANGE `p_view` `p_view` INT( 6 ) NULL DEFAULT NULL
Same column is now used with ORDER BY query.
SELECT * FROM `y21_11` WHERE dir='sql_tutorial/' ORDER BY p_view DESC

SQL String References Update Query

Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net



    Simple and direct solution. Thanks.
    Rizwan Akber Ali


    You are so sweet! Its really very simple way of learning. Thanks!


    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


    Argument data type ntext is invalid for argument 1 of replace function. Can I get around this?




    thanks very much. direct to point

    Post your comments , suggestion , error , requirements etc here

    SQL Video Tutorials

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer