Came across an interesting problem over the weekend. I had a MySQL database table that contains a column of MS-DOS directory paths. I was writing a PHP program to search this column and it took a few minutes to work out how many delimiting backslashes I needed. It was a bit fiddly and ought to be the type of thing you can just look up so here it is!

The best way to show this is by doing it wrong and explaining why.

SELECT id, folder FROM folder WHERE folder LIKE "%\White\%";

This won't work because "\W" will be converted into a single character which won't be a "W" any more and "\%" will be converted in to a single character that won't be a "%" any more. We need to delimit the text to preserve the backslashes so the command can be submitted.

If we add another set of backslashes to form the following then we cam drop into the next level - the SQL engine parser.

SELECT id, folder FROM folder WHERE folder LIKE "%\\White\\%";

The SQL engine parser reads the statement as "%\White\%" and so we have the same problem we had with the command line stage.

So lets add another set of backslashes to the statement.

SELECT id, folder FROM folder WHERE folder LIKE "%\\\White\\\%";

Still not enough backslashes. This time we'll get "\" followed by a single character "\W", which still won't match.

So to get to our quarry we must add another set of backslashes.

SELECT id, folder FROM folder WHERE folder LIKE "%\\\\White\\\\%";

The backslashes survive all the way through the command line, through the search engine to allow us to search for a single backslash in a string.

Now comes the fun bit. I needed to embed this SQL line into a PHP command, so I need to delimit all the backslashes i.e. double them all up.

$sql = "SELECT id, folder FROM folder WHERE folder LIKE \"%\\\\\\\\White\\\\\\\\%\"";

..and don't forget we need an extra set of backslashes to protect the quotation marks around the search filter.