Symfony2 LOWER DQL function and Character not in repertoire error

Question

I'm working with Symfony 2.3 and PostgreSQL 9.2. I just want to perform two simple queries, according to the value of a checkbox. Such queries are:

1) SELECT * FROM table WHERE field LIKE 'Something'
2) SELECT * FROM table WHERE field ILIKE 'Something'

As you know, the first is case sensitive, while the second is case insensitive. I know that in Doctrine ILIKE does not exist and that I should use the LOWER function. However, if I specify some special characters in the query (for example 'è'), I get an error when I use the case insensitive version (that is, when using the LOWER function).

In particular, the error I get is the following:

SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xe3 0xa8 0x20

Everything works fine when I do not use the LOWER function. I already checked the encoding and it is UTF8 for both the database and the client_encoding parameter of PostgreSQL.

Any suggestion? Thank you

It's easy enough to add ILIKE to Doctrine. Start here: http://symfony.com/doc/current/cookbook/doctrine/custom_dql_functions.html — Cerad, May 25 '14 at 18:44
Well...I have tried that solution and the result is the same :( Exactly the same error... the strange thing is that if execute exactly the same query in PgAdmin it works! .. Any other suggestion? — user1923631, May 26 '14 at 11:49
Then it is an encoding issue of some sort. I'm not very good at those. — Cerad, May 26 '14 at 19:54

score 1 · Answer 1 · answered Jan 12 '17 at 13:49

It's first result in google, but there is no answer.

Answer: If you use UTF-8 encoding (or other multibyte encoding) on website, you must use PHP functions with 'mb' to process texts.

I had this problem with:

$qb = $this->createQueryBuilder('cf');
$qb->select(['cf'])
    ->where($qb->expr()->orX(
        'LOWER(cf.name) LIKE :searchName',
        'LOWER(cf.address) LIKE :searchName',
        'LOWER(cf.city) LIKE :searchName',
        'LOWER(cf.zipCode) LIKE :searchName'
        ));
$qb->setParameter('searchName', '%' . strtolower($searchName) . '%');

You just need to replace strtolower with mb_strtolower

score 0 · Answer 2 · answered May 18 '21 at 13:12

For Symfony 4^
As mentionned in another answer, you can substitute the functionality using the expr() methods of doctrine and simulate the "iLIKE" PostgreSQL function like so :

// inside your method in your repository class    
$this
        ->createQueryBuilder('doctor')
        ->add('where', $this->expr()->orX(
            $this->expr()->like($this->expr()->lower('doctor.firstName'), ':complete_name'),
            $this->expr()->like($this->expr()->lower('doctor.lastName'), ':complete_name')
        ))
        ->setParameter('complete_name', "%$complete_name_criteria%");

Don't forget to add this use statement (however you will get an unexpected error..) :

use Doctrine\ORM\Query\Expr\Join;

Symfony2 LOWER DQL function and Character not in repertoire error

2 Answers2