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. — Alex Howansky
– Alex Howansky, Commented Apr 14, 2017 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 sids easy. — Dhruv Saxena
– Dhruv Saxena, Commented Apr 14, 2017 at 22:28

O. Jones · Accepted Answer · 2017-04-14 22:44:59Z

1

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.

answered Apr 14, 2017 at 22:44

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hafizudin Johari Over a year ago

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

Collectives™ on Stack Overflow

SQL finding specific character in table

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related