laravel:how to use LOWERCASE Function in Eloquent Query?

Question

I want to implement case insensitive search following is my code for search

/*get the title to search*/
$newsTitle=Input::get('srach_newsTitle')?Input::get('srach_newsTitle'):'';
$query = DB::table('news');
if( strlen($newsTitle) )
    {
        $query->whereRaw('LOWERCASE(`newsTitle`) LIKE ? ',[trim(strtolower($newsTitle)).'%']);
    }

but its says function LOWERCASE is not Defined in My projects following is the error message

QueryException in Connection.php line 729: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION cpr-laravel.LOWERCASE does not exist (SQL: select count(*) as aggregate from news where LOWERCASE(newsTitle) LIKE test%)

whats wrong i am doing?plz help

try this one strtolower

RamAnji
– RamAnji

2017-09-15 11:43:41 +00:00
Commented Sep 15, 2017 at 11:43 — RamAnji
– RamAnji, Commented Sep 15, 2017 at 11:43

aaron0207 · Accepted Answer · 2017-09-15 11:46:47Z

43

Use LOWER()

$query->whereRaw('LOWER(`newsTitle`) LIKE ? ',[trim(strtolower($newsTitle)).'%']);

answered Sep 15, 2017 at 11:46

aaron0207

2,3531 gold badge20 silver badges25 bronze badges

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

1 Comment

3und80 Over a year ago

still working today! I only added '%'. before trim to allow more wildcard searches

Akbarali · Accepted Answer · 2022-07-15 07:20:35Z

The above code is good but has some problems. The main thing is that letters in Krill cannot be lowercase

Here's a sample that worked for me

$search = mb_strtolower(trim(request()->input('search')));

$query->whereRaw('LOWER(`newsTitle`) LIKE ? ',['%'.$search.'%']);

I wanted to search for data stored in multi-language JSON, and this worked for me.

public function search()
    {
        $search = mb_strtolower(trim(request()->input('search')));
        $products = DB::table('products')
            ->whereRaw("LOWER(JSON_EXTRACT(name, \"$.".app()->getLocale()."\")) LIKE ?", ["%".$search."%"])
            ->whereNull('deleted_at')
            ->select(['id', 'name', 'slug',])
            ->orderBy('id', 'desc')
            ->limit(15)->get();

        return $products;
    }

Bhargav Raviya · Accepted Answer · 2023-03-12 11:12:52Z

1

Use LOWER()

$query->whereRaw('LOWER(`newsTitle`) LIKE ? ',[strtolower(trim($newsTitle)).'%']);

The above answer is right. But "trim" after using "strtolower" is faster working.

answered Mar 12, 2023 at 11:12

Bhargav Raviya

4695 silver badges8 bronze badges

1 Comment

trckster Over a year ago

You mean "strtolower" after "trim" is faster? According to your code.

Collectives™ on Stack Overflow

laravel:how to use LOWERCASE Function in Eloquent Query?

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related