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

Question

I am having table where a column allows special characters like '/' forward slash and '' back slash.

Now when I am trying to search such records from table, I am unable to get those.

example : abcdef or abc/def I am generating search query which is like

select * from table1_1 where column10 like '%abcdef%'

It is returning 0 rows but actaully there is 1 record existing. how to write a query in this case, let me know.

Thanks.

Answer 1

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, “ ” to represent a newline character), you must double any “” that you use in LIKE strings. For example, to search for “ ”, specify it as “ ”. 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.