Query - where 'field' IN (array of wildcarded strings) - possible?

Continuing the discussion from Incorrect search query result:

We’ve seen code like this that passes an array to a query

$array = array("this", "that", "another");
$in_list = implode(",", $array);
$query = "SELECT field FROM table WHERE field IN ($in_list);

the query being equivalent to (i.e. array changed to a list)

$query = "SELECT field FROM table WHERE field IN ("this", "that", "another");

And we’ve seen code that uses wildcards in a query

$query = "SELECT field FROM table WHERE field LIKE "%h%";

But short of building a query that uses ORs

OR field LIKE "%his";
OR field LIKE "tha%";
OR field LIKE, "%nothe%";

I know of no way to pass an array of wildcarded strings to a MySQL WHERE


Maybe it could be done using a database other than MySQL?

1 Like

[quote=“Mittineague, post:1, topic:215294, full:true”]Impossible?

pretty much, yeah


But even if it was possible… would it be worth it? It looks like it could take ages to run a query like that

yes, it wouldn’t be wise to comb a large database for every field containing any of the letters “d”, “o”, “n”, “t”.

Actually my examples are short but not closely representative.

The germ for this pondering was that the member that posted the topic this was branched from has a field like
“first middle last”

Users might search for any combination of two of the three, and the result should be found. So what is needed is.

“first middle%”, “first%last” and “%middle last”

Not so bad with only the three variations, And putting together an “OR _ OR _ OR _” isn’t too difficult.

And in this case a single “%input%input%” would work.

More that it’s just looking for an easier better way. But sometimes work is work after all.

1 Like

You can use REGEXP:

SELECT field FROM table WHERE field REGEXP '(his$|^tha|nothe)'

However, regular expressions in MySQL are quite primitive, for example they will not work well with utf-8 character sets.

Postgresql has very good regular expression support and will work for utf-8 characters as well, so it is better suited for languages other than English or Latin in this case (however, much more limited in the choice of collations).

1 Like

This query nearly works, it is case insensitive but must have:

  1. FULLTEXT index
  2. complete words only
 SELECT   * 
  FROM     cities2
  WHERE    MATCH (name, state_name)
  AGAINST ('+sasony -halle' IN BOOLEAN MODE);  

// more parameters

  • stands for AND
  • stands for NOT
    [no operator] implies OR

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.