CODEWITHSUNDEEP
mysqlsqldatabase
Adam
Adam

Reputation: 533

Odd behaviour when doing LIKE with wildcards searching for backslash in MySQL

I've encountered a very unusual problem with MySQL, involving backslashes. Basically when I do a wildcard match with LIKE for \n which is in the database as text rather than an actual newline, it will only return a match if I have just a right hand wildcard:

SELECT * 
FROM  `tmptest` 
WHERE  `a` LIKE  '\\\\n%'

Now, if I query like this, it will not return anything:

SELECT *  
FROM `tmptest` 
WHERE `a` LIKE '%\\\\n%'

As you can see from the data I have in the table both queries should be matching. I'm not sure if it's something that I'm missing, or I'm incorrectly escaping the newline but it doesn't make sense for the first query to work and the second not.

Table structure:

CREATE TABLE IF NOT EXISTS `tmptest` (
`a` varchar(22) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Sample data:

INSERT INTO `tmptest` (`a`) VALUES
('\\n'),
('\\n\\ndfsdfsdfs\\n');

Thanks for taking time to read this.

Upvotes: 7

Views: 1465

Answers (2)

Dam
Dam

Reputation: 577

Based on the QSP answer (As I thanks him for saving my day).

I would say use 5 blackslashes. I don't understand why like QSP and Karolis, but if you replace 1 slash by 5 slaches in mySQL (or 10 in php), it still work as QSP say.

BUT the good thing, is if you replace '\\' by 12 backslashes (or 24 in php) as QSP suggest, it won't work. But if replace it by 10 backslashes (or 20 in php) it will still work.

I hope that can help someone as QSP answer is already accepted.

EDIT :
It work fine if you do :
%\\\\\%
%\\\\\\\\\\%
%k\\\\\\\\\\s%
%\\\\\\\\\\s%
%k\\\\\s% (if only 1 slash like back\slash)
But it doesn't work if you do :
%k\\\\\\\\\\%
%k\\\\\%
%\\\\\s%
I will continue to look why.

Upvotes: 0

Bruno Flávio
Bruno Flávio

Reputation: 778

It works for me with 6 backslashes when using the left side wildcard:

mysql> SELECT * FROM `tmptest` WHERE `a` LIKE '%\\\\\\n%';
+-----------------+
| a               |
+-----------------+
| \n              |
| \n\ndfsdfsdfs\n |
+-----------------+
2 rows in set (0.00 sec)

Using mysqld Ver 5.1.49

@Karolis as far as i understand the expression for the LIKE operator should be parsed twice, hence \\\\ turns into \ when used with LIKE.

But how to explain this (using the expression "back\slash"):

SELECT 'back\\slash' LIKE '%back\\\\slash%'; -> TRUE (normal behaviour)

SELECT 'back\\slash' LIKE '%back\\\\\slash%'; -> TRUE (5 backslashes?)

SELECT 'back\\slash' LIKE '%back\\\\\\slash%'; -> TRUE (6 backslashes?)

SELECT 'back\\slash' LIKE '%back\\\\\\\slash%'; -> TRUE (7 backslashes?)

SELECT 'back\\slash' LIKE '%back\\\\\\\\slash%'; -> FALSE (normal behaviour, I guess..)

Nevertheless if searching only for the "\":

mysql> SELECT 'back\\slash' LIKE '%\\\\%'; --> FALSE (but should work)

mysql> SELECT 'back\\slash' LIKE '%\\\\\%'; --> TRUE (5 backslashes)

mysql> SELECT 'back\\slash' LIKE '%\\\\\\%'; --> TRUE (6 backslashes)

mysql> SELECT 'back\\slash' LIKE '%\\\\\\\%'; --> FALSE (7 backslashes)

For this particular question, one could use a different escape character | and bypass the problem altogether (if no | character occurs):

mysql> SELECT 'back\\slash' LIKE '%\\%' ESCAPE '|'; --> TRUE

So maybe some mysql guru out there can explain this. I simply can't. also tested with mysql 5.1.53 on a different machine. Same behaviour was observed. As i started by commenting, its a rather interesting question.

Upvotes: 7

Related Questions