TransWikia.com

Laravel like in where don't return result if space exist before character

Stack Overflow Asked by pysql on December 5, 2021

I don’t know how to explain more my question; on laravel I need to return members has code start with (J or j) but when I use ('members.code', 'like', 'j%');

there are some members in var_dump of code are like " J3X", " jh4", " j5T". my code is returning only those who are like "jH6"; "J9u" … but not if they have space or more than one sapce in my result.

I want to return all members that they have code start with j even there is space or more than one space in code.

my query is

$query = member::where('members.inscription', '>=', $date_start->startOfDay())
                ->where('members.inscription', '<=', $date_end->endOfDay())
                ->where('members.id_member', '>', 1099)
                ->where('members.code', 'like', 'J%')
                ->orderBy('members.id_member', 'DESC');

$members = $query->get();

var_dump($members); die;

One Answer

You could try trimming whitespace on the left of the member codes before doing a LIKE comparison:

$query = member::where('members.inscription', '>=', $date_start->startOfDay())
    ->where('members.inscription', '<=', $date_end->endOfDay())
    ->where('members.id_member', '>', 1099)
    ->whereRaw("LTRIM(members.code) LIKE 'J%' OR LTRIM(members.code) LIKE 'j%'")
    ->orderBy('members.id_member', 'DESC');

But note that using LTRIM this way (or some alternative, such as REGEXP) would make the WHERE clause non sargable, meaning that any index on members.code can no longer be used. If this be a concern to use performance-wise, then consider scrubbing your data to remove this leading whitespace.

Answered by Tim Biegeleisen on December 5, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP