Problem with LIKE

Hello,

I have a problem with LIKE operator. If I write :

WHERE path LIKE 'D:\test\January\cost.txt'

the result is nothing but I'm sure it should get at least one row. I found out that the problem is the character '\' therefore if I write:

WHERE path LIKE 'D:_test_January_cost.txt'

I get one row result. Am I doing something wrong ? Is it a bug ? How can I handle if I have some text with '\' ?

Thank you for your help

MM

The backslash \ escapes the following character. If you want to not do that, just duplicate it:

WHERE path LIKE 'D:\\test\\January\\cost.txt'

Note, this is a requirement in MySQL and MariaDB, probably not on other servers.

... and of course you can replace the LIKE with an equal sign:

WHERE path = 'D:\\test\\January\\cost.txt'

Further, that underscore _ is a wildcard character for a LIKE clause. One underscore represents exactly one arbitrary character - including the backslash but it may then be too inexact, imagine such a value also matches:

'D:\test-January-cost.txt'

So prefer the duplicated backslash to be most exact.

To make it even more confusing, the underscore may get escaped through a preceding backslash, e.g. if you want it not to be a wildcard:

WHERE path LIKE 'D:\\test\\January\_2025\\cost.txt'

Thank you for the reply. I tested and this what I got: WHERE path LIKE 'D:\%' --> nothing WHERE path LIKE 'D:\\%' --> more rows found WHERE path LIKE 'D:\test%' --> nothing WHERE path LIKE 'D:\\test%' --> nothing

Please note that I'm working with Mariadb 11.5.2

Regards MM

Sorry for the wrong format !

Thank you for the reply. I tested and this what I got:

WHERE path LIKE 'D:\%' --> nothing

WHERE path LIKE 'D:\\%' --> more rows found 

WHERE path LIKE 'D:\test%' --> nothing 

WHERE path LIKE 'D:\\test%' --> nothing

Please note that I'm working with Mariadb 11.5.2

Regards

MM

Please enclose code in backticks, so duplicated backslashes are not removed by my forum software:

You duplicated the backslash correctly, in two of your lines. Forget the other lines.

Now is your question why you don't get rows? I can only guess there are none, because I don't see your table contents here.

Hello,

sorry for not posting correct. Of course I have

SELECT path FROM archive

before the WHERE line. In the meantime I found out the solution, the line should be:

WHERE path LIKE 'D:\\\\test\\\\January\\\\cost.txt'

Yes I need 4 '\'.

Thank you in any case for your help. Regards.

MM

Can you show some of the resulting rows? Do they perhaps have duplicated backslashes?

Uh, you're right - it must have 4 backslashes, as documented: https://dev.mysql.com/doc/refman/8.4/en/string-comparison-functions.html#operator_like