MySQL Forums :: Newbie :: Correct syntax to search for backslashes "\"


Advanced Search

Correct syntax to search for backslashes "\"
Posted by: Dr Lightman ()
Date: November 05, 2010 05:44PM

Today I was given the task to clean up rows replacing \' with '

First I run a query like that to have a general look:

select name from table where name like '%\\\'%'

But too many rows were returned, such as:

aaa'bbb
ccc\'ddd

I didn't understand why "aaa'bbb" was returned, since it doesn not contain any backslash.

Tried with '%\\'%' and '%\'%' but for obvious reasons they didn't work, they gave 0 rows or threw a syntax error.

Regardless these unfortunate attempts, the replace query worked:

update table set name = replace(name,'\\\'','\'')

What I'd like to know is why the in REPLACE \\\' works as expected, and when used in the LIKE statement it did not. More generally, in the LIKE statement, even using \\ to search for a single backslash, returns 0 rows.

Thanks.

Options: ReplyQuote


Subject Written By Posted
Correct syntax to search for backslashes "\" Dr Lightman 11/05/2010 05:44PM
Re: Correct syntax to search for backslashes "\" Rick James 11/07/2010 01:48PM
Re: Correct syntax to search for backslashes "\" Dr Lightman 12/12/2010 03:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.