I cannot replace part of the name in registry with update, set, replace in MYSQL

Question

I was using UPDATE SET REPLACE in order to replace certain content of a name in many registries but for some reason is not working now.

UPDATE stores
SET tit_doc = REPLACE( tit_doc, 'WORKSHOP WEST COAST NUMBER 3', ' ' )
WHERE store_loc LIKE "%3A12%"

This way I replace 'WORKSHOP WEST COAST NUMBER 3' with nothing, so basically remove that from all the registries selected, but it shows

Affected rows: 0 (Query took 0.1036 sec)

I did try

SELECT REPLACE (tit_doc, 'WORKSHOP WEST COAST NUMBER 3', ' ' ) replace_result
FROM stores
WHERE store_loc LIKE "%3A12%"
ORDER BY `replace_result` ASC 
LIMIT 0 , 30

and it shows

Showing rows 0 - 29 (50 total, Query took 0.0609 sec)

And displays all the registries that have those characters in the location asked.

How can I do this correctly?

Does `SELECT * FROM stores WHERE store_loc LIKE "%3A12%"` return any records? — Tyler Roper, Sep 07 '18 at 18:37
Does your query (select) show record with WORKSHOP...3 removed? — DanB, Sep 07 '18 at 18:39
Does this get any results back? `SELECT * FROM stores WHERE store_loc LIKE "%3A12%" AND tit_doc LIKE '%WORKSHOP WEST COAST NUMBER 3%';` _Also, keep in mind possible issues with case sensitivity._ — Uueerdo, Sep 07 '18 at 18:47
@Uueerdo OP had mentioned they got an `Affected Rows: 0` back from the update, leading me to believe that the `REPLACE` is likely not the source of the issue. Even if the `REPLACE` didn't occur, it would still have returned the count of records satisfied by `LIKE "%3A12%"`. — Tyler Roper, Sep 07 '18 at 19:01
@TylerRoper I think that is a connection option; unlike other RDBMS, MySQL's records affected is usually the records actually changed. I remember because it used to cause headaches when I had to work with it through linked tables in MSAccess. — Uueerdo, Sep 07 '18 at 19:11
@Uueerdo Ah! In that case, my apologies, you may very well be right. I'm a SQL Server guy and didn't consider the possibility that would be different across RDBMS. Thanks! — Tyler Roper, Sep 07 '18 at 19:43
It is weird, when i use SELECT * FROM stores WHERE store_loc LIKE "%3A12%" AND tit_doc LIKE '%WORKSHOP WEST COAST NUMBER 3%' don't get any results back — Therealguma, Sep 07 '18 at 20:12
And if i go to the website where the info is been displayed, i do CTRL + F and paste WORKSHOP WEST COAST NUMBER 3 i get 50 results — Therealguma, Sep 07 '18 at 20:15

score 0 · Answer 1 · answered Sep 08 '18 at 03:08

I believe the source of your problem is that you are mixing single quotes and double quotes inside your sql. They are not interchangable. Single quotes are used to identify a string. Double quotes are used for identifiers, such as a column alias or table name. The WHERE clause in your UPDATE statement is using double quotes. Try changing it to single quotes.

To add to the confusion, this behavior is dependent on a server setting as well as the type of statement you run.

See When to use single quotes, double quotes, and back ticks in MySQL and https://dev.mysql.com/doc/refman/8.0/en/string-literals.html.

I tried either way and it still the same... but i found a solution. — Therealguma, Sep 10 '18 at 16:00

score 0 · Accepted Answer · edited Nov 20 '20 at 12:29

I tried to replace half of the string I wanted to replace and it worked, so I tried a longer string and so on, I get to a point where it shows no results and nothing can get replaced.

So what I did is to replace up to the point where it let me, and after that replace (with another query) what was left.

The example I used in my question is not the actual name I have (because it has very specific information about my job). So I think it might have to do with some limitations in the long of the string you want to replace.

I cannot replace part of the name in registry with update, set, replace in MYSQL

2 Answers2