mysql - How to handle query search with special characters /(forward slash) and \(backslash)

Barmar is partially correct (so +1),

So the trick is to double escape ONLY the backslash, for string escapes only a single escape is needed.

For example

  • The single quote ' only needs escaping once LIKE '%\'%'
  • But to query backslash \ you need to double escape to LIKE '%\\\\%'
  • If you wanted to query backslash+singlequote \' then LIKE '%\\\\\'%' (with 5 backslashes)

Explanation Source excerpt:

Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\n”. To search for “\”, specify it as “\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.


In MySQL, this works:

select * from Table1
where column10 like '%abc\\\\def%'

FIDDLE

Backslash is an escape prefix for both strings and LIKE patterns. So you need to double it once for LIKE, and again for string literal syntax.

Tags:

Mysql

Plsql