How to do a column search scan with counter bar character

Question

I am trying to perform a query in a table similar to the one below through a field diretorio.

+----------+---------------+----------------------------------------+
|id_imagem |  data_upload  |               diretorio                |
+----------+---------------+----------------------------------------+
|   1      | 2019-05-18    |   \\xxx.xxx.xxx.xx\xyzdnas\img1.png    |
|   2      | 2019-05-18    |   \\xxx.xxx.xxx.xx\dasdadg\img2.png    |
|   3      | 2019-05-18    |   \\xxx.xxx.xxx.xx\gnwwefi\img3.png    |
|   4      | 2019-05-18    |   \\xxx.xxx.xxx.xx\gdfgthh\img4.png    |
|   5      | 2019-05-18    |   \\xxx.xxx.xxx.xx\njfsdfu\img5.png    |
+----------+---------------+----------------------------------------+

The column I use for condition is the diretorio, however, I only have the IP address and the folder the files are in, and I need to get all the images that are in that folder from the BD.

I was initially trying to search:

SELECT * FROM db.tb WHERE diretorio LIKE '%\\xxx.xxx.xxx.xx\njfsdfu%';

However, the search does not return the values correctly, I did some searches in Google and found searches like these below but also failed:

SELECT * FROM db.tb WHERE diretorio LIKE '%\%\%xxx.xxx.xxx.xx\%njfsdfu%' ESCAPE '|';

SELECT * FROM db.tb WHERE diretorio LIKE '%\\\\xxx.xxx.xxx.xx\\njfsdfu%' ESCAPE '|';

but I could not get a result.

Does anyone know how to do this search?

PS: I’m using the MySQL and the column field diretorio is VARCHAR(600)

Answer 1

One possibility is to use the function LOCATE from MYSQL, The function returns the position of the first occurrence of a substring in a string. If the substring is not found, this function returns 0.

SELECT * FROM db.tb where locate("\\xxx.xxx.xxx.xx\\njfsdfu\\", diretorio) > 0;

or use the like to escape the reverse bars.

SELECT * FROM tb WHERE diretorio LIKE '%\\xxx.xxx.xxx.xx\\njfsdfu%' ESCAPE '|';

Mysql LIKE vs LOCATE