Correct syntax to search for backslashes "\"

[Dr Lightman]

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.