SQL - Query to find if a string contains part of the value in Column

Question

I am trying to write a Query to find if a string contains part of the value in Column (Not to confuse with the query to find if a column contains part of a string).

Say for example I have a column in a table with values

ABC,XYZ

If I give search string

ABCDEFG

then I want the row with ABC to be displayed.

If my search string is XYZDSDS then the row with value XYZ should be displayed

Answer 1

The answer would be "use LIKE".

See the documentation: https://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

You can do WHERE 'string' LIKE CONCAT(column , '%')

Thus the query becomes:

select * from t1 where 'ABCDEFG' LIKE CONCAT(column1,'%');

If you need to match anywhere in the string:

select * from t1 where 'ABCDEFG' LIKE CONCAT('%',column1,'%');

Here you can see it working in a fiddle: http://sqlfiddle.com/#!9/d1596/4

Answer 2

Select * from table where @param like '%' + col + '%'

Answer 3

First, you appear to be storing lists of things in a column. This is the wrong approach to storing values in the database. You should have a junction table, with one row per entity and value -- that is, a separate row for ABC and XYZ in your example. SQL has a great data structure for storing lists. It is called a "table", not a "string".

If you are stuck with such a format and using MySQL, there is a function that can help:

where find_in_set('ABC', col)

MySQL treats a comma delimited string as a "set" and offers this function. However, this function cannot use indexes, so it is not particularly efficient. Did I mention that you should use a junction table instead?