sql query with \ as value to check

Question

I have a query in the following way.....i have a column named location_ns and it contains 'Marriott\256 Hotel & Convention Centre' as it's value. But query is not resulting any id. when i remove \ in both value and query it is working.

SELECT id
FROM notice
WHERE 
lon = 78.48693966865540000000 
AND lat = 17.42434596639255000000 
AND location_ns = 'Marriott\256 Hotel & Convention Centre' 
AND notice_type="text"

Please let me know if any one has a solution for it

Try to escape it using two \'s instead of one: `Marriott\\256 Hotel & Convention Centre` — h2ooooooo, Nov 30 '12 at 15:50

score 2 · Accepted Answer · edited May 23 '17 at 10:24

As documented under String Literals:

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the escape sequences shown in Table 9.1, “Special Character Escape Sequences”. For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, “\x” is just “x”.

Therefore, you must escape your backslash:

AND location_ns = 'Marriott\\256 Hotel & Convention Centre'

However, you would do well to pass your literals to MySQL as parameters to a prepared statement.

score 1 · Answer 2 · answered Nov 30 '12 at 15:51

\ is an escape sequence in MySQL. If you had a record with value:

Marriott\256 Hotel & Convention Centre

You would need to query for it like

AND location_ns = 'Marriott\\256 Hotel & Convention Centre'

This let's MySQL know you are looking for a literal \.

sql query with \ as value to check

2 Answers2