How to check to see if a value exist in a VARCHAR column - mysql

Sure, but with caveats:

You could do it like this:

DECLARE @text NVARCHAR(MAX);
SET @text = 'Item3'

SELECT t.* 
FROM   table t
WHERE  t.Items like '%'+@text+'%'

That is 'ok' but far from great. It will identify Item3 AND Item35.

You could try using the commas in a multipart select:

DECLARE @text NVARCHAR(MAX);
SET @text = 'Item3'

SELECT t.* 
FROM   table t
WHERE  t.Items like @text+',%'
OR     t.Items like ', %'+@text+',%'
OR     t.Items like ', %'+@text
OR     t.Items = @text

This uses the comma to check if the text is an item, at the start or end of a collection or if it is the only item.

However, and here comes the caveat, text bases searches can be very slow, especially if they are large or you have many items to find. It may be better to convert the csv columns into tables with a tabular function like the one linked here:
Convert a CSV delimited string to table column in SQL SERVER[^]

This could also be slow too. A combination of the first "like" query and this one may be more efficient.


My long term advice is to not store csv in a table column. It should be simple enough to store csv values in a child table. This is far more efficient especially if the Items are not unique to any one record. However, I am aware of many reasons why you cannot redesign the db at this stage.
EDIT: You should read OriginalGriff's points on this issue

I hope that helps ^_^
Andy