CODEWITHSUNDEEP
sqlmysqlrelational-database
akosch
akosch

Reputation: 4406

Pattern match within the IN operator (MySQL)

I would like to match patterns inside an IN expression like this:

... WHERE ... IN ('%alma%','%sajt%')

Is it possible somehow and if not, how could I achieve this some other way?

Upvotes: 2

Views: 2123

Answers (5)

phil_w
phil_w

Reputation: 1264

If you're doing something like me, it might be helpfull to insert the patterns in a secondary table. Then you may use 'like' with a 'join':

create table patterns ( p varchar(50)); insert into patterns values ( 'to%'), ('lo%');

create table table1 ( txt varchar(50)); insert into table1 values ('toto'),('titi'),('tom'),('louis');

select txt from table1 inner join patterns on txt like p;

| toto |

| tom |

| louis |

This works for me but oddly enough it is much slower than several calls (on my 5.5 system at least): select txt from table1 where txt like 'to%'; select txt from table1 where txt like 'lo%'; ...

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 51000

Other posters have showed you how to use multiple LIKE clauses combined with OR to get this effect. Here are two other thoughts:

  • If you find yourself searching inside text fields it is often an indication that you are storing too much information in those fields. If possible, consider breaking out those values into separate fields and / or tables to make searching and validation easier.

  • Plain SQL does not excel at searching inside text fields. If the text is large, you have more than a relatively small number of records, and performance is important for this query, consider adding full text indexing to your database.

Upvotes: 1

Fosco
Fosco

Reputation: 38526

You would need to either use multiple LIKE statements

(columnName LIKE '%alma%' or columnName LIKE '%sajt%' or columnName LIKE '%etc%')

Or look into Full Text Search: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Upvotes: 3

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50037

As far as I'm aware this is not allowed. You'd have to use multiple LIKE expressions, as in

SELECT *
  FROM SOME_TABLE
  WHERE SOME_VALUE LIKE '%alma%' OR
        SOME_VALUE LIKE '%sajt%';

Share and enjoy.

Upvotes: 0

Alin P.
Alin P.

Reputation: 44346

It's not possible. IN is designed to search in potentially large sets, not to make pattern searches.

You should use multiple LIKE conditions separated by OR operators.

Upvotes: 2

Related Questions