MYSQL WHERE LIKE Statement

Question

I am attempting to use the LIKE clause in a mysql statement as follows:

SELECT * FROM batches WHERE FilePath LIKE '%Parker_Apple_Ben_20-10-1956%'

The data matched data within the 'FilePath' Column is:

C:\SCAN\Parker_Apple_Ben_20-10-1830\TEST

The above SQL statement works fine and picks up the relevent row, but if I for example add the "\" character into the end of the LIKE clause (like follows) it does not pick up the row correctly:

SELECT * FROM batches WHERE FilePath LIKE '%Parker_Apple_Ben_20-10-1956\%'

Funnily enough though if I place a '\' character at the beginning of LIKE clause (like follows) it picks up the row perfectly fine - this has me baffled.

SELECT * FROM batches WHERE FilePath LIKE '%\Parker_Apple_Ben_20-10-1956%'

Answer 1

Within LIKE, _ is a wildcard matching a single character and so that needs escaping to correctly match a literal '_' instead of potentially matching anything. Additionally you are mentioning trying to match a string ending in 1830 with a like ending 1956. Finally as mentioned by J W and the MySQL documentation you need to escape backslashes twice

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

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.

Try

SELECT * FROM batches 
WHERE FilePath LIKE '%Parker\_Apple\_Ben\_20-10-1830\\\\%'

http://sqlfiddle.com/#!2/3ce74/3

Answer 2

adding \ in the current query escapes %, try this

SELECT * FROM batches WHERE FilePath LIKE '%Parker\_Apple\_Ben\_20-10-1956\\\\%'

• SQLFiddle Demo

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.

Answer 3

Use quadruple escape:

SELECT * FROM batches WHERE FilePath LIKE '%Parker_Apple_Ben_20-10-1956\\\\%'

Explanation here

Answer 4

You should escape your '\' character. Try this:

SELECT * FROM batches WHERE FilePath LIKE '%Parker_Apple_Ben_20-10-1956\\%'

Answer 5

This is because \% is an escape character in MySQL, for more information, see the following:

https://stackoverflow.com/a/881208/1666167

Answer 6

You should escape special characters like \, ', _ etc in mysql like \\ , \', \_