0

Table name: student:

id    name     topics
---   ----     --------
1     Test1    1,2,10,15,25
2     Test2    5,21,11,18,13
3     Test3    2,1,16,25,10
4     Test4    2

My query: 

select * from student where topics like '%2%'

output: all 4 records. Expected: But i need to get only 3 record since that id 1,3,4 topics column contains 2. 2nd record doesn't contain 2 .

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Binaya BM
  • 13
  • 3
  • It technically contains 2 that is 21 having 2. Your wild card searches for 2 only. – saravanatn Jun 06 '18 at 12:53
  • 2
    The 2 in 25 & 21 is like %2%. You should never store delimited values in a column, you should use a one-to-many intermediate table with multiple studentId -> topic rows. – Alex K. Jun 06 '18 at 12:53

3 Answers3

1

You can use the function FIND_IN_SET :

select * from student where FIND_IN_SET ('2', topics)
A. Colonna
  • 852
  • 7
  • 10
0

The second record DOES contain 2. You need to use something like this.

WHere topics=2 or topics like '2,%'or topics like '%,2' or topics like '%,2,%'

The first checks if topics is 2. THe second if 2 is the first, but there are others. THe third checks if 2 is the last, but there are some numbers before, and the forth checks if 2 is somewhere in between

kkica
  • 4,034
  • 1
  • 20
  • 40
0

Something like that might help?

SELECT * FROM student WHERE topics LIKE '2,%' OR topics LIKE '%,2,%' OR topics LIKE '%,2' OR topics= '2';

its sad that you cant use IN with LIKE.

Guillaume Georges
  • 3,878
  • 3
  • 14
  • 32
Konstantinos Tsirogiannis
  • 1
  • 1