How do I run Update Query in MySQL and replace a matching string

Question

I tried a couple of things but it deleted the complete content from the column.

Here's what I want to do - A particular table [abc_tablename] in my MySQL database has a column [xyz_columnname] that has a lot of email IDs (more than 5000) and I want them to replace all the email IDs with a text [email ID removed]

I used the following query for SELECT:

SELECT * 
  FROM abc_tablename
 WHERE xyz_columnname LIKE '%@%'

How do I perform the replace function in this case? Can you help me with the query for the same. I tried this but it removed all the data from xyz_columnname

UPDATE abc_tablename 
   SET xyz_columnname = REPLACE (  xyz_columnname,  LIKE ''%@%'',  '**email ID removed**');

I'm using PHPMyAdmin to run these queries. Also, do note that xyz_columnname has a lot of text and numeric content along with the email ID.

Is the value in xyz_columnname just an email address and nothing else? Or is it an email address among a bunch of other text like blah blah [email protected] blah and you only want to replace the email in that text? If that is the case, you would likely need to use a regular expression to perform a replace. Mysql doesn't have a built in regexp replace function so you would need to use something like php and update it or define a udf version of regexp_replace (there are several out there). — Jonathan Kuhn
– Jonathan Kuhn, Commented Apr 27, 2017 at 22:48
@JonathanKuhn You mentioned it correct. The xyz_columnname has a lot of text and numeric content along with the email ID. — ravichopra
– ravichopra, Commented Apr 28, 2017 at 5:57
@ravichopra, well, like I said above, mysql doesn't have the capability out of the box to do pattern match and replace. You would either need to use something else like php and preg_replace to pull out all the rows where like %@%, fix them and update the rows one at a time or implement one of the many regexp_replace user defined functions within mysql use that. The latter would likely be more performant, but if you are doing this as a one off thing, just brute force it the easier way with php and in the future, fix it before insert. — Jonathan Kuhn
– Jonathan Kuhn, Commented Apr 28, 2017 at 17:04

Brendan Gooden · Accepted Answer · 2017-04-27 22:17:39Z

1

You can simply use this command, this will update all rows which column xyz_columnname contains an @ to show email ID Removed

UPDATE abc_tablename 
    SET `xyz_columnname` = 'email ID removed'
    WHERE `xyz_columnname` LIKE '%@%'

Use ` (backquotes) for column name

Use ' or " for values

Don't use backticks with column values. use either single or double quotes otherwise mysql will consider that value as a column name.

edited Apr 27, 2017 at 22:17

answered Apr 27, 2017 at 22:02

Brendan Gooden

1,5913 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ravichopra Over a year ago

Will this work even if the xyz_columnname has has a lot of text and numeric content along with the email ID?

Brendan Gooden Over a year ago

No, this will only work if xyz_columnname only contains email address. You didnt make that clear in your question that xyz_columnname contained multiple types of information. This is not best practice for SQL, you should use a separate column for each value.

Collectives™ on Stack Overflow

How do I run Update Query in MySQL and replace a matching string

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related