How to fetch record on the basis of any one value from the column of two comma separated values

Question

May be the Question's title is not correctly defined what i actually wanted to ask. Here is the more specific description of my question

I have a following table User in my database which has a column i.e. Category which contains multiple values but separated by commas

S.no.     Name         Category

 1       Ankit     Ex Soldier, Senior Citizen

 2       Ritu      Widow, Senior Citizen

 3       Akash     Ex soldier

I wanted to search the record on the basis of category

for eg. If i search

select * from User where Category='Senior Citizen'

Then it must show Ankit and Ritu record. How to do this. plz help

Answer 1

try this:

select * from User where Category like '%Senior Citizen%'

Answer 2

You need LIKE operator:-

select * from User where Category LIKE '%Senior Citizen%'

Answer 3

select * from User where Category LIKE '%Senior Citizen%'

But you should use a separate table for Category.

Answer 4

Like Kiss László wrote you should separate the information in two tables. The professional term for this is called "Normalization". Most important to know are the 1NF, 2NF and 3NF (read this for detailed information).

So it should look like the following:

Table Persons
PersonId    Name
1           Ankit
2           Ritu
3           Akash

Table Categories
CategoryId    Name
1             Ex. Soldier
2             Senior Citizen
3             Widow

Table PersonCategories
PersonId    CategoryId
1           1
1           2
2           2
2           3
3           3

Why should you do this?

In my opinion the biggest reason is performance. I made some test table with your current approach with a data set of 20k entries. The execution of the query took about ~200ms to return. With the schema above the following query executed in about ~1ms

SELECT
  *
FROM
  Persons AS p
JOIN
  PersonCategories AS pc ON p.PersonId = pc.PersonId
JOIN
  Categories AS c on pc.CategoryId = c.CategoryId
WHERE
  c.Name = 'Senior Citizen'

Why is this query so much faster?

Because we can easily use indices on our columns. In the schema above the Persons.PersonId and Categories.CategoryId are the PRIMARY KEY columns of their tables. So to use them as a column for a JOIN operation has minimal costs. Both columns of the PersonCategories table are FOREIGN KEYS (ensures a valid database state and improves performance). Finally the Categories.Name column has an INDEX too.

Could this approach be bad?

In most cases this is the way to go. One reason not to do it this way, is if you have to handle lots of INSERTS. INSERTS in this schema have a much higher cost because all indices need to be updated after the INSERTS.