Search code examples
mysqlplsql

How to handle a query with special characters / (forward slash) and \ (backslash)

I have a table where a column allows special characters like '/' (forward slash) and '' (back slash).

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

For example: abc\def or abc/def

I am generating a search query like:

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

It is returning 0 rows, but actually there is 1 record existing that should be returned. How do I write the query in this case?

Solution

  • 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:

    MySQL uses C escape syntax in strings (for example, "\n" to represent the newline character). If you want a LIKE string to contain a literal "\", you must double it. (Unless the NO_BACKSLASH_ESCAPES SQL mode is enabled, in which case no escape character is used.) 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.

    Exception: At the end of the pattern string, backslash can be specified as "\\". At the end of the string, backslash stands for itself because there is nothing following to escape.