SQL finding specific character in table

Question

I have a table like this

d_id    | d_name | d_desc | sid
1       |flu     | ....   |4,13,19

Where sid is VARCHAR. What i want to do is when enter 4 or 13 or 19, it will display flu. However my query only works when user select all those value. Here is my query

SELECT * FROM diseases where sid LIKE '%sid1++%'

From above query, I work with PHP and use for loop to put the sid value inside LIKE value. So there I just put sid++ to keep it simple. My query only works when all of the value is present. If let say user select 4 and 19 which will be '%4,19%' then it display nothing. Thanks all.

You could do this with regex but that's usually a bad idea. Database fields should not contain more than one value. See [first normal form](https://en.wikipedia.org/wiki/First_normal_form). — Alex Howansky, Apr 14 '17 at 22:28
In the long run, it'll be good to create a new table that holds one row for each `(d_id, sid)` combination. It'll also make querying by one or all `sid`s easy. — Dhruv Saxena, Apr 14 '17 at 22:28
That was so fast! thanks. @DhruvSaxena, actually I have created a new table but it went wrong where I lost my idea. That is why I try another method — Hafizudin Johari, Apr 14 '17 at 22:32
@AlexHowansky I try use the wildcards but wrong. Do you have any query that can help me? — Hafizudin Johari, Apr 14 '17 at 22:34
There's a [question](http://stackoverflow.com/q/5033047/2298301) asked before which could illustrate how comma separated values can be scanned and searched. However, honestly speaking, I personally would be very reluctant to endorse that as a solution to this question. — Dhruv Saxena, Apr 14 '17 at 22:41

score 1 · Accepted Answer · answered Apr 14 '17 at 22:44

If you must do what you ask for, you can try to use FIND_IN_SET().

SELECT d_id, d_name, d_description
  FROM diseases
 WHERE FIND_IN_SET(13,sid)<>0

But this query will not be sargable, so it will be outrageously slow if your table contains more than a few dozen rows. And the ICD10 list of disease codes contains almost 92,000 rows. You don't want your patient to die or get well before you finish looking up her disease. :-)

So, you should create a separate table. Let's call it diseases_sid.

It will contain two columns. For your example the contents will be

   d_id    sid
      1     4
      1    13
      1    19

If you want to find a row from your diseases table by sid, do this.

SELECT d.d_id, d.d_name, d.d_description
  FROM diseases d
  JOIN diseases_sid ds ON d.d_id = ds.d_id
 WHERE ds.sid = 13

That's what my colleagues are talking about in the comments when they mention normalization.

Hey thanks for your answer. Actually I have tried the second method but it went wrong as when user select only one sid 4(sore throat) then it will display all disease including the chronic one such as cancer. So i decided to change the method as I have no idea how to solve it — Hafizudin Johari, Apr 14 '17 at 23:34

SQL finding specific character in table

1 Answers1