MySQL Replace character from column

Question

I would like to replace all occurences of 's with s in myTable's name column. I tried REPLACE but it replaces all records. I want to do it for some customers.

Note: Customer is foreign key in myTable.

Thanks in advance

Update:

I tried some queries REPLACE(name, "'s", "s") from data where cust_id = 1 but this doesn't work

From one of questions I tried SELECT REPLACE(name, "'s", "s") from data where cust_id = 1. This is replacing all records in table

yes i tried where clause but it is not accepting where clause — User16119012
– User16119012, Commented Feb 10, 2014 at 17:29
It's remotely possible that you are simply using it wrong. There's just no way that your DBA has disabled the WHERE clause. — Álvaro González
– Álvaro González, Commented Feb 10, 2014 at 17:30

Ryan · Accepted Answer · 2014-02-10 17:39:09Z

4

A simple replace across all records in the table can be done with:

 UPDATE myTable
 set    name = REPLACE(name, '\'s', 's')

If you want to filter the records to be updated from another table, then you can do something like

 UPDATE myTable
 LEFT JOIN CustomerTable ON myTable.Customer = CustomerTable.Customer
 SET    name = REPLACE(name, '\'s', 's')
 WHERE  CustomerTable.Field > SomeCondition <== Replace with what you need.

answered Feb 10, 2014 at 17:39

Ryan

28.4k10 gold badges58 silver badges83 bronze badges

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

1 Comment

User16119012 Over a year ago

Thanks man. Why can't I thaught to use replace with update. Anyways, this helped me alot.

Collectives™ on Stack Overflow

MySQL Replace character from column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related