How to fetch a single value from multivalue attribute in sql

Question

I have one table called student.Table has 4 column.

Student : Table

Id Name    RollNo  SubjectCode
1  Rani    B0101   1,3,5,7,12
2  Samidha B0102   1,2,4,6,12

I want to find out name of student whose has subjectcode 2.

Query:

select name from student where charindex('2',subjectcode) > 0

But problem occure when subjectcode contain 12.Because 12 contain 2.How to solve it?

On a related side note: why do you have CSV in one of your columns? Isn't it an option to normalize that data? — Jeroen, Feb 06 '14 at 12:46
PS. I think the actual question you ask may in essence be a duplicate of [this one on splitting CSV in SQL](http://stackoverflow.com/q/314824/419956) — Jeroen, Feb 06 '14 at 12:47

score 2 · Answer 1 · answered Feb 06 '14 at 12:58

Your query doesn't work because it will match 12 as well as 2.

You can fix this by appending the delimiters on either side:

select name
from student
where charindex(',2,', ','+subjectcode+',') > 0;

That said, you really need another table, which is the association of students and subjects. This table, say StudentSubjects has two important columns: StudentId and SubjectCode. If you had such a table, this query would be quite easy using standard SQL constructs.

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

Split the CSV value and use the IN clause, like this:

 select name from student where '2' IN Split(',', SubjectCode)

For Split use, for example, the function mentioned in this question.

edited May 23 '17 at 11:51

Community

1
1

answered Feb 06 '14 at 12:50

Jeroen

60,696
40
206
339

How to fetch a single value from multivalue attribute in sql

2 Answers2