'Doctrine LIKE case insensitive
is it possible to make a search with Doctrine case insensitive?
Solution 1:[1]
This depends mainly on your Database-Server. A LIKE with MySQL is case insensitive a like with PostgreSQL is case sensitive. But you can help yourself with something like this:
$pattern = strtolower('HEllO WorlD');
$q = Doctrine_Query::create()
->select('u.username')
->from('User u')
->where("LOWER(u.username) LIKE ?", $pattern);
Solution 2:[2]
Also, you can try:
$queryBuilder->where('LOWER(b.title) LIKE LOWER(:query)')
->setParameter('query', '%' . $query . '%');
Important: After converting a string that contains special characters to lower case with strtolower(), the special characters don’t appear correct.
Solution 3:[3]
Other thing you can do is:
$qb->andWhere($qb->expr()->like('lower(o.name)', ':name'));
$qb->setParameter('name', '%'. strtolower($search) . '%');
Regards.
Solution 4:[4]
The best way to do this is to have a canonized username column, in that column you can store the lowercase version of the username column and any other canonization process you want to do and search and index that column instead of the username column, then just do
->where("u.canonic_username) LIKE ?", $pattern);
That way you can use indexes and all of that.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | abranhe |
| Solution 2 | |
| Solution 3 | Nicolas Finelli |
| Solution 4 |