How to search case insensitive in Eloquent model

Question

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

score 65 · Answer 1 · edited Jun 16 '21 at 18:44

65

Actually, 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.

edited Jun 16 '21 at 18:44

Pezhvak

7,358
7
26
38

answered Sep 10 '18 at 02:26

Randy Dryburgh

791
1
4
4

41

This does NOT work on MySQL as it does not support `ILIKE`. – Putr Oct 04 '19 at 13:41
6

Worked at my PostgreSQL server. – Mamun Morshed Apr 29 '20 at 18:57
3

This works ONLY for PostgreSQL but doesn't work for MySQL – Liga May 08 '20 at 07:48

Ts8060 · Accepted Answer · 2020-06-21T07:06:35.310

17

I suggest Upper function in this case

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

like this

edited Jun 21 '20 at 07:06

answered Jul 24 '18 at 12:04

Ts8060

990
7
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
18

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
9

@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

score 9 · Answer 3 · answered Jan 26 '21 at 06:52

9

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();

answered Jan 26 '21 at 06:52

Thanushka

170
4
12

Unicco · Answer 4 · 2020-08-25T12:52:07.217

5

This should work.

Replace column and $text.

$model = Models::whereRaw( 'LOWER(`column`) LIKE ?', [ $text ] )->first();

edited Aug 25 '20 at 12:52

answered Aug 25 '20 at 09:51

Unicco

2,176
1
21
28

score 4 · Answer 5 · answered Jul 24 '18 at 11:59

4

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.

answered Jul 24 '18 at 11:59

guiCunha

165
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

score 4 · Answer 6 · answered Jul 16 '20 at 10:42

4

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

Just use:

Controller::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

answered Jul 16 '20 at 10:42

Koiki Damilare

142
1
6

Well, that is what I'm using right now. And it works just fine – Koiki Damilare Aug 10 '20 at 09:52
5

"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
I don't know how to show you a screenshot of what I'm working on. I mean, I'm also surprised that it works in Laravel without comparing cases. I hope you understand what I'm trying to say. – Koiki Damilare Aug 12 '20 at 19:22

score 1 · Answer 7 · edited Apr 29 '21 at 12:29

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.'%');
});

Peace Ngara · Answer 8 · 2018-07-24T12:19:23.520

-10

This has been answered use iLike operator it's case insensitive see

Laravel Eloquent Ignore Casing

Or for fun do this, not recommended ..

// prepare two variants of the same value ?
// first to uppercase
// second to lowercase

$valueUp    = strtoupper($value);
$valueLower = strtolower($value);

Model::where($column, 'LIKE', '%' . ($valueUp || $valueLower) . '%' );

Did not test it but that's the idea to win ..

edited Jul 24 '18 at 12:19

answered Jul 24 '18 at 12:05

Peace Ngara

601
5
7

2

It won't work. If DB record consist Upper and lower letters – Ts8060 Jul 24 '18 at 12:08
Then that database design will be a poorly designed database, prepare content before inserting in the DB, nothing is automatic in programming. before inserting that specific record prepare it for querying ] – Peace Ngara Jul 24 '18 at 12:10
2

You have user named "John" in your db. And you want to get this record when user search like "jo". How will you design DB in this situation? – Ts8060 Jul 24 '18 at 12:13
2

($valueUp || $valueLower) => is true .. so your query would be: 'LIKE', '%true%' ).... – Simon Fakir Apr 24 '19 at 15:58
So? How do you do this? – Matteo Gaggiano May 22 '19 at 12:04
if its camel case then ?? – rameezmeans Nov 28 '19 at 12:27
@PeaceNgara storing strings that are not entirely uppercase or entirely lowercase is certainly not a poorly designed database... that's a pretty ridiculous generalization. How do you think Amazon's product descriptions are stored, or Medium's blog posts? Do you think StackOverflow converts this comment to lowercase or uppercase when it stores it, and then adds casing back in based on punctuation when displaying the comment on the page? that Is nOT HoW thEir DaTaBaSe WORKS. In fact, in my opinion, that would be more indicative of a poorly designed database. – Air Jul 04 '21 at 21:23
If you har using Postgresql then `ILIKE` works perfectly fine. https://www.postgresql.org/docs/12/functions-matching.html#FUNCTIONS-LIKE – Fractalf Aug 30 '21 at 09:04
The conditional has no dynamic effect on the SQL? The fact that no one mentioned that is literally mind blowing... @SimonFakir is the only one close but booleans don't output as a string so the result would literally be `%%` – zanderwar Apr 26 '22 at 09:03

How to search case insensitive in Eloquent model

8 Answers8

Linked