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

Please clarify your question and fully describe how it should work with more examples. There are too many unknowns at present. Does your column always contain 3 characters? Can you match anywhere in the match string (not just at the start)? — Turophile, Apr 01 '15 at 11:21

score 29 · Accepted Answer · answered Apr 01 '15 at 11:32

29

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

answered Apr 01 '15 at 11:32

Turophile

3,367
1
13
21

1

How is the performance impact if the column1 is indexed? Is it going to be slower? – Ayman Hussain Oct 09 '19 at 06:31
`CONCAT` is the key ingredient here. If you try `'%' + column1 + '%'` it won't work – Pierre Apr 19 '23 at 07:10

score 4 · Answer 2 · edited Mar 08 '19 at 16:41

4

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

edited Mar 08 '19 at 16:41

Eric Brandt

7,886
3
18
35

answered Apr 01 '15 at 11:16

Aheho

12,622
13
54
83

1

Thanks. It helps. Just a small modification. Select * from table where @param like '%' || col || '%' – user2354254 Apr 01 '15 at 11:20
This didn't work for me. The correct answer is Turophile's – besimple Jan 21 '16 at 08:48
This didn't work for me also. When the answer needs modification why have you makred it as accepted answer @user2354254. – userab Mar 03 '17 at 06:45

score 3 · Answer 3 · answered Apr 01 '15 at 11:29

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?

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

3 Answers3

Linked