45

I want to search case-insensitive in Eloquent model.

Now I am using this

Model::where($column, 'LIKE', '%' . $value . '%' );

But it is case sensitive. How can I solve this?

I also find this post How can I search (case-insensitive) in a column using LIKE wildcard? but I can not use it in Eloquent model

Mhluzi Bhaka
  • 1,364
  • 3
  • 19
  • 42
Davit Zeynalyan
  • 8,418
  • 5
  • 30
  • 55

8 Answers8

77

If you are using Postgres, you don't need to use UPPER, just use ilike as the comparator and it will do a case-insensitive comparison.

Model::where('column', 'ilike', '%' . $value . '%')

You do need the % signs to signify the substring you're searching for.

dustbuster
  • 79,958
  • 7
  • 21
  • 41
Randy Dryburgh
  • 927
  • 1
  • 5
  • 4
  • 54
    This does NOT work on MySQL as it does not support `ILIKE`. – Putr Oct 04 '19 at 13:41
  • 7
    Worked at my PostgreSQL server. – Mamun Morshed Apr 29 '20 at 18:57
  • 6
    This works ONLY for PostgreSQL but doesn't work for MySQL – Liga May 08 '20 at 07:48
  • 'ilike' or 'ILIKE' will not work in My SQL, for My SQL @Liga you'll need to use upper() OR lower() for making your search string case insensitve... `select * from users where upper(first_name) / lower(first_name) like %(your string)%` don't forget to %% and your string in between those percents, this'll help you to make search more flexible by searching substrings too... – Maddy Jul 07 '23 at 18:37
19

I suggest Upper function in this case

Model::whereRaw("UPPER('{$column}') LIKE '%'". strtoupper($value)."'%'");

like this

Ts8060
  • 1,030
  • 8
  • 19
  • 3
    I think you have an extra *dot* and extra single quotes i.e. it should be Model::whereRaw("UPPER('$column') LIKE '%". strtoupper($value)."%'"); – SuperNOVA Dec 12 '18 at 12:36
  • 21
    it's seems not secure – id614515 Jun 14 '19 at 10:42
  • 1
    @id614515 can someone elaborate, why it is insecure and how to improve? – Origami1024 Jul 10 '20 at 10:19
  • 13
    @Origami1024 This is NOT secure because it's susceptible to SQL injections. You should use parameter binding like this: `Model::whereRaw('UPPER('{$column}') LIKE ?', ['%' . strtoupper($value) . '%']);` – Michiel Jan 07 '21 at 19:25
  • If the above doesn't work for you. Try to specify the table e.g. `UPPER( table_name.column_name )` (no quotes around table_name.column_name) – tenshi May 01 '22 at 01:38
  • Be careful using the above answer as that is vulnerable to sql injection. Sanitize the input first. – tenshi May 01 '22 at 01:46
15

Didn't see this solution. so I'm posting this here.

DB::table('products')
   ->select('productid')                
   ->where(DB::raw('lower(product)'), 'like', '%' . strtolower($searchword) . '%')
   ->get();
Thanushka
  • 381
  • 2
  • 5
  • 14
  • Use of `DB::raw()` is discouraged in modern code, which is why all other answers are using `Builder::whereRaw()`. – miken32 May 03 '23 at 20:18
7

This should work.

Replace column and $text.

$model = Models::whereRaw( 'LOWER(`column`) LIKE ?', [ $text ] )->first();
Unicco
  • 2,466
  • 1
  • 26
  • 30
6

Laravel eloquent search is case insensitive, you don't need ilike, upper or lower functions in your query.

Just use:

Model::query()
    ->where('column1_name', 'LIKE', "%{$search}%") 
    ->orWhere('column2_name', 'LIKE', "%{$search}%") 
    ->orWhere('column3_name', 'LIKE', "%{$search}%") 
    ->get();

The orWhere part is in case you have more than one table field that you want to query

Damilare Koiki
  • 341
  • 3
  • 11
  • Well, that is what I'm using right now. And it works just fine – Damilare Koiki Aug 10 '20 at 09:52
  • 7
    "Like" operator is case-sensitive in some cases and in other not, according to db collation, see https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html#operator_like – OMR Aug 12 '20 at 18:03
5

What's your collation of tables?

This is a particularity of the collation in your database, try using latin_general_ci or latin_gerneral_cs, they are the insensitive case.

But if you are trying to get this in utf8_bin by example, it will not run because this collation is sensitive.

All examples are running under MySql.

guiCunha
  • 343
  • 1
  • 4
  • 1
    I am using `utf8_bin` but I dont wont change it – Davit Zeynalyan Jul 24 '18 at 12:01
  • So, I believe it will not be possible this search, the limitation will be in your table collation choice, any kind of switch on the code will affect this particularity. I don't try it yet, but you can create another connection with the database and on your database config PHP file, and set in that array 'collation' => 'utf8_general_ci'. – guiCunha Jul 24 '18 at 17:09
  • Thank you for this. I didn't know that if my table is utf8_unicode_ci, the "ci" means it is case-insensitive, so I don't need to use `ilike` or other approaches here. https://dba.stackexchange.com/a/8031/18098 – Ryan Aug 19 '19 at 18:46
0

You do not necessarily have to convert both your keyword and the DB value to the same case (UPPER or LOWER). Rather you may simply specify "iLIKE" instead of "LIKE". Following Example returns list of model data based on category_id and importantly whether the keyword matches either first name or last name:

$transactions = Transaction::where('category_id', $category_id);
$transactions = $transactions->where(function ($q) use($keyword) {               
    $q->where('firstName', 'iLIKE', '%'.$keyword.'%');
    $q->orwhere('lastName', 'LIKE', '%'.$keyword.'%');
});
Dharman
  • 30,962
  • 25
  • 85
  • 135
Dibyendu Mitra Roy
  • 1,604
  • 22
  • 20
0

The best option is to use BINARY

User::select("id", "name", "email")
                    ->where(DB::raw('BINARY `name`'), "Daniel")
                    ->get();
Rajat Masih
  • 537
  • 1
  • 6
  • 19