I'm trying to get a value from a normalized database by using a comma-delimeted string as the search query. The intention is that it should take the comma delimeted string and break it into seperate pieces that MySql can then match against the tags in the normalized database.

Example: My normalized key database looks like this:

Table: Key

It will get R_Name from a table called Responsibility.

I will be querying with a value like: T1, T2, T6. I would like MySql to recognize these as separate values, without the commas, and match them with values in the table. I am using a custom program that outputs a comma delimited list. There is no other way to output the values in a feasible way.

I have tried something like this:

SELECT DISTINCT RE.R_Name
FROM Demo_Tag DT 
INNER JOIN Responsibility RE ON RE.R_id = DT.R_id
WHERE FIND_IN_SET(DT.Tag_id, "T18, T5") <> 0;

But it didn't work.

If I can have something that operates similar to this:

SELECT DISTINCT RE.R_Name
FROM Demo_Tag DT 
INNER JOIN Responsibility RE ON RE.R_id = DT.R_id
WHERE DT.Tag_id IN ("T4", "T18", "T19");

but with a T1, T2, T3 value: that would be ideal.

Thank you in advance for your assistance. I am still learning SQL & I find it better to ask for help where I can. I have read up on FIND_IN_SET and I am not sure if it helps in this instance.

CodePudding user response：

FIND_IN_SET() doesn't work if you have spaces in your comma-separated list:

mysql> select find_in_set(456, '123, 456, 789') as pos;
 ----- 
| pos |
 ----- 
|   0 |
 ----- 

mysql> select find_in_set(456, '123,456,789') as pos;
 ----- 
| pos |
 ----- 
|   2 |
 ----- 

But besides that, FIND_IN_SET() is not a good way to search because it can't be optimized with an index. Your search is bound to do a table-scan and have poor performance.