SQL query to search in a sting for a specific value

Question

I made a C# program that stores questions and answers in a SQL database. To be more accessible I've added the option to search in a column called Tags that have up to 5 words split by comma. I tried the following code but it checks only the first value of the Tags:

SELECT
    Id, Observations, Problem, Solution, Tags
FROM            
    IT
WHERE        
    (RTRIM(LTRIM(SUBSTRING(Tags, 1, CHARINDEX(',', Tags, 0) - 1))) IN (@a, @b, @c, @d, @e)) OR
    (RTRIM(LTRIM(SUBSTRING(Tags, CHARINDEX(',', Tags,0), CHARINDEX(',', Tags, CHARINDEX(',', Tags,0)) - 1))) IN (@a, @b, @c, @d, @e)) OR
    (RTRIM(LTRIM(SUBSTRING(Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags,0)), CHARINDEX(',', Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags,0))) - 1))) IN (@a, @b, @c, @d, @e)) OR
    (RTRIM(LTRIM(SUBSTRING(Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags,0))), CHARINDEX(',', Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags,CHARINDEX(',',Tags,0))))) - 1))) IN (@a, @b, @c, @d, @e)) OR
    (RTRIM(LTRIM(SUBSTRING(Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags,CHARINDEX(',',Tags,0))))), CHARINDEX(',', Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags, CHARINDEX(',', Tags,CHARINDEX(',',Tags,CHARINDEX(',',Tags,0))))))) - 1))) IN (@a, @b, @c, @d, @e))

I'd appreciate if your answer contains an explaining. Thanks!

That's why you should never store comma separated values. For your case you can use `LIKE` operator or do the parsing in C# code. — Rahul, Jun 14 '16 at 16:52
Why not create a separate table with tags and then a many-to-many table to link them to your existing table? Having comma separated values in a DB is in general a bad design. — juharr, Jun 14 '16 at 16:53
Thank you guys! I used @juharr 's solution, so I added a new table. — Andrei David, Jun 14 '16 at 17:45

score 0 · Answer 1 · edited May 23 '17 at 12:24

While people have already commented and down voted you for your question. And I agree if you have program and DB design control I would suggest storing the tags in a separate many to many table.

However, there are actually plenty of valid reasons to store a concatenated value in a database and sometimes you will need to spit or search the values. Run a search for split concatenated string to rows. Here is a stackoverflow question on it already. Turning a Comma Separated string into individual rows

If that is too much trouble @Rahul mentions you can use LIKE in which case you would actually do something like

 WHERE [Tags] LIKE '%' + @a + '%'
OR WHERE [Tags] LIKE '%' + @b + '%'

etc.. The problem with this approach is say someone tags the word "things" and someone else tags another record "something" and the user searches for "thing" both would be returned.

you can also go the route you are going and do some crazy substring magic. The trick to troubleshooting that would be to create columns with the where conditions to see what values you are actually getting and tweak your statements as needed. Without a dataset I am not going to try to figure out what is wrong with the index positions. I personally would chop the string up though.

I am writing an answer because I lack the 50 reputation to just write a comment....

Thank you for your answer! I finally did it by adding a new table — Andrei David, Jun 14 '16 at 17:46

SQL query to search in a sting for a specific value

1 Answers1