What's wrong with my sql when I using FIND_IN_SET function?

Question

In my table called testtb has a column that storing file path,Now I want to find the filed which start in C:\.I use the sql,but it always return 0.what's wrong?

--example 
select  FIND_IN_SET('C:\\', 'C:\\abc.png' )
-- my sql
 select  FIND_IN_SET('C:\\', filepath) from testtb;

Here is my table data:

id,    filepath,               srcfilepath
'1', 'C:\20160101\abc.jpg', 'C:\20160101\abc.jpg'
'2', 'D:\20160101\abc.jpg', 'D:\20160101\abc.jpg'
'3', 'E:\TP\20160101\abc.jpg', 'E:\TP\20160101\abc.jpg'

score 2 · Accepted Answer · edited Jan 06 '22 at 03:57

2

You are just confused. find_in_set() refers to sets of values in MySQL. In MySQL, these are values separated by commas: '1,2,3' or 'abc,def,ghi'.

I think you are looking for the instr() or position() function:

select instr('C:\\abc.png', 'C:\\')

edited Jan 06 '22 at 03:57

Nimantha

6,405
6
28
69

answered Apr 11 '16 at 02:22

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Apr 11 '16 at 02:29

find_in_set is not proper here, and like will do what you expect.

mysql> select  'C:\\abc.png' LIKE 'C:\\\\%';
+------------------------------+
| 'C:\\abc.png' LIKE 'C:\\\\%' |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

What's wrong with my sql when I using FIND_IN_SET function?

2 Answers2