How to give an UPDATE by swapping for ?

Question

I am trying to perform an UPDATE to change the \\ for \ , as in SQL below:

UPDATE `post` SET `descricao`= replace(descricao, '\\','\');

I was told that Mysql does not accept regular expression on UPDATE and that I should wear the REPLACE, but it’s syntax error. Anyway, how do I fix it?

Answer 1

The backslash has special significance in strings. It indicates that the next character is special and should not be treated as a normal character. At the same time it takes some special character and transforms it into normal character. What are the special characters?

Obviously the quotes are special to close the string, so if you want quotes to be part of the text the only way to do this is to put a backslash first to say that it’s a normal character and you don’t want to close the text.

Also if you want a backslash to be a normal character you need to put a backslash before it so that the former has the special meaning and the latter is the normal character.

So when you just put a backslash and then quotes it is not closing the text (which is necessary) and causes the syntax error. To work you need to put the backslash twice, so only one will be considered the bar as a character and then closes quotes normally without error.

On the other there is no syntax error but there is semantic error of what you want. As it has two bars only one will be considered for the same reason above. So if you want to represent two backslashes as normal characters you need to put 4 of them so the first two form a real bar and the next two form the second real bar. Thus:

UPDATE post SET descricao = replace(descricao, '\\\\','\\');

I put in the Github for future reference.

I’m not saying that it satisfactorily solves what you want because it has no details and is not the focus of the question.

Answer 2

try this. replace it does the following, takes the first parameter and exchanges it for the second parameter. or wherever you have it will exchange it for . UPDATE post SET Description = replace(Description, '\','\');