SQL LIKE % inside array

Question

I know how to perform an SQL LIKE % query for a single value like so:

SELECT * FROM users WHERE name LIKE %tom%;

but how do I do this if the search terms for my LIKE comes from an array? For example, let's say we have an array like this:

$words = array("Tom", "Smith", "Larry");

How do I perform my SQL LIKE % to search for the words in my array like:

SELECT * FROM users WHERE name LIKE %[each_element_from_my_array]%

WITHOUT putting the whole query inside a foreach loop or something

EDIT : I forgot to mention that I'm doing this in cakePHP inside the conditions of the cakePHP find('all') method, so that complicates things a little bit.

Thanks

Your question has at least 5 votes for the like-operator tag. Could I kindly request that you suggest sql-like as a synonym? — Kermit
– Kermit, Commented Apr 2, 2013 at 18:41

472084 · Accepted Answer · 2012-02-23 17:32:09Z

35

$sql = array('0'); // Stop errors when $words is empty

foreach($words as $word){
    $sql[] = 'name LIKE %'.$word.'%'
}

$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);

Edit: code for CakePHP:

foreach($words as $word){
    $sql[] = array('Model.name LIKE' => '%'.$word.'%');
}

$this->Model->find('all', array(
    'conditions' => array(
        'OR' => $sql
    )
));

Read up on this stuff: http://book.cakephp.org/1.3/en/view/1030/Complex-Find-Conditions

edited Feb 23, 2012 at 17:32

answered Feb 23, 2012 at 15:35

472084

17.9k10 gold badges67 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jovan Perovic Over a year ago

my thought exactly. nice answer! just need to be careful when having an empty array which would cause SQL error...

Jovan Perovic Over a year ago

I think OR 0 would do just fine... ;)

user765368 Over a year ago

This will work, but how do I do this inside the cakePHP find('all') function (See edit)

Anthony Johnston Over a year ago

I know this is old, but just struck me how this would be open to sql injection - what with security being a hot topic atm thought I'd raise it

vartec · Accepted Answer · 2012-02-23 15:36:30Z

32

In case of standard SQL, it would be:

SELECT * FROM users WHERE name LIKE '%tom%' 
                       OR name LIKE '%smith%' 
                       OR name LIKE '%larry%';

Since you're using MySQL you can use RLIKE (a.k.a. REGEXP)

SELECT * FROM users WHERE name RLIKE 'tom|smith|larry';

answered Feb 23, 2012 at 15:36

vartec

135k38 gold badges227 silver badges248 bronze badges

3 Comments

Ilia Ross Over a year ago

RLIKE happened to be an absolutely great solution for me, thanks!! ;)

Dan D Over a year ago

my column 'name' needs to be wrapped in 'lower' function, does it make the query slow on multiple 'or's and 'like's?

Zoraiz Over a year ago

RLIKE saved the day. Thanks a bunch, kind internet stranger

Marc B · Accepted Answer · 2012-02-23 15:34:34Z

4

You can't. It'll have to be a chained field like %..% or field like %..% or .... A where ... in clause only does extract string matches, with no support for wildcards.

answered Feb 23, 2012 at 15:34

Marc B

362k44 gold badges433 silver badges508 bronze badges

Comments

John Woo · Accepted Answer · 2012-02-23 15:37:06Z

3

try using REGEXP

SELECT * FROM users where fieldName REGEXP 'Tom|Smith|Larry';

answered Feb 23, 2012 at 15:37

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Mani · Accepted Answer · 2013-12-18 09:35:28Z

0

i just took the code of 472084.

$sql = array('0'); // Stop errors when $words is empty

foreach($words as $word){
    $sql[] = 'name LIKE %'.$word.'%'
}

$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);

For my self, i had to modify it because it's returned me an error SQL. I Post it for people who gonna read the thread.

foreach($words as $word){
    $sql[] = 'name LIKE \'%'.$word.'%\'';
}

$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);

The difference between them is about quote, my mysql DB said there is a problem ! so i had to escape quote from $sql[] = 'name LIKE %'.$word.'%' and now it's work perfectly.

edited Dec 18, 2013 at 9:35

Mani

17.6k13 gold badges81 silver badges103 bronze badges

answered Dec 18, 2013 at 9:08

user3114471

1532 silver badges11 bronze badges

Comments

André Mendonça · Accepted Answer · 2015-09-23 13:57:08Z

Blockquote

/** * Implode a multidimensional array of values, grouping characters when different with "[]" block. * @param array $array The array to implode * @return string The imploded array */ function hoArray2SqlLike( $array ) { if ( ! is_array( $array ) ) return $array;

$values  = array();
$strings = array();

foreach ( $array as $value )
{
    foreach ( str_split( $value ) as $key => $char )
    {
        if ( ! is_array( $values[ $key ] ) )
        {
            if ( isset( $values[ $key ] ) )
            {
                if ( $values[ $key ] != $char )
                {
                    $values[ $key ]     = array( $values[ $key ] );
                    $values[ $key ][]   = $char;
                }
            }
            else
                $values[ $key ] = $char;
        }
        elseif ( ! array_search( $char , $values[ $key ] ) )
            $values[ $key ][] = $char;
    }
}

foreach ( $values as $value )
{
    if ( is_array( $value ) )
        $value = '['.implode( '', $value ).']';

    $strings[] = $value;
}

return implode( '', $strings );

}

shashikant pandit · Accepted Answer · 2016-08-24 07:39:47Z

In this code we can't get double quotes in sql query

$sql = array('0'); // Stop errors when $words is empty

foreach($words as $word){
    $sql[] = 'name LIKE %'.$word.'%'
}

$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);

SELECT * FROM person where name like %"Tom"% OR name like %"Smith"% OR name like %"Larry"%;
// error in double quotes and % is out side of Double quotes .

Or you can also use comma separated value:-

$name = array(Tom, Smith, Larry);

$sql="SELECT * FROM person";

    extract($_POST);
    if ($name!=NULL){
    $exp_arr= array_map('trim', explode(",",$name));
    echo var_export($exp_arr);
    //die;
        foreach($exp_arr as $val){
        $arr = "'%{$val}%'";
        $new_arr[] = 'name like '.$arr;
        }

      $new_arr = implode(" OR ", $new_arr);
      echo $sql.=" where ".$new_arr;
    }
        else {$sql.="";}

Echo sql query like this:-

SELECT * FROM person where name like '%Tom%' OR name like '%Smith%' OR name like '%Larry%';

mehmet · Accepted Answer · 2021-05-04 10:52:02Z

0

try this: With just one extra line of code :)

$words = array("Tom", "Smith", "Larry"); 

$words = implode("%' OR `name` LIKE '%", $words);
// result -> Tom% OR `name` LIKE %Smith% OR `name` LIKE %Larray

$menus = "SELECT * FROM `users` WHERE `name` LIKE '%" . $words . "%'";
// result -> "... WHERE `name` LIKE %Tom% OR `name` LIKE %Smith% OR `name` LIKE %Larray%";

edited May 4, 2021 at 10:52

answered May 4, 2021 at 10:25

mehmet

7347 silver badges13 bronze badges

1 Comment

Nico Haase Over a year ago

Please share more details - is this any better than the existing answer?

IIF_Hater · Accepted Answer · 2019-06-18 16:14:00Z

-2

Many SQL systems let you push it as a character value. CHAR(10) = Linefeed. So, occasionally, instead of working out all the escape coding, you can push single quotes, double quotes and other things into a sql string this way, without too many other coding issues.

-Just a hint.

answered Jun 18, 2019 at 16:14

IIF_Hater

1

2 Comments

Jeroen Heier Over a year ago

Hi, welcome to stack overflow. Explain why this is an answer to the question.

Nico Haase Over a year ago

Please share more details - how is this even related to the given question?

Collectives™ on Stack Overflow

SQL LIKE % inside array

9 Answers 9

4 Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

4 Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related