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;
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP