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 onceLIKE '%\'%'
- But to query backslash
\
you need to double escape toLIKE '%\\\\%'
- If you wanted to query backslash+singlequote
\'
thenLIKE '%\\\\\'%'
(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.