How to replace and update specific value in sql

Published on Author bloggerfive

We can use update and replace methods to to change particular column value to another value in sql. But we need to be very careful when we run the update  query.

Always perform the select query and check the output. Then proceed with update query

Assume if table column contains ‘I like ‘Calcutta’, you need to  replace all fields which contain text ‘Calcutta’ with ‘Kolkata’ .

Syntax

UPDATE [tablename]

SET [fieldname] = REPLACE ([fieldname], ‘text to find’, ‘text to replace with’)

WHERE [fieldname] LIKE ‘%text to find%’

Example

UPDATE city_details

SET city_name = REPLACE(city_name, ‘Calcutta’, ‘Kolkata’)

WHERE city_name LIKE ‘%Calcutta’%’

Remember you cannot use replace with regex function .

How it works

Replace

It finds specific value from the string or table column then Just replace with desired value.

Example:

mysql> SELECT REPLACE(‘www.howwhywhat.com’, ‘com’, ‘in’);     

       Output: ‘www.howwhywhat.in’

Update

As the name suggest, it used to modify permanently the field value/column value in the database.

Example:

mysql> UPDATE domain
SET domain_name =
www.howwhywhat.in’
Where domain_name like ‘%what%’;

      Output: ‘www.howwhywhat.in’

Comments

comments