How to combine lower and like in ZF2?

Question

How to get SQL like this :

select * from foo where LOWER(foo_name) like '%test%';

I know that I can achieve this:

select * from foo where LOWER(foo_name) = 'test';

By:

$where->addPredicate(new Predicate\Expression('LOWER(foo_name) = ?', 'test' ));

And this:

 select * from foo where foo_name like '%test%';

By:

$where->addPredicate( new \Zend\Db\Sql\Predicate\Like('LOWER(foo_name)', '%test%'));

But how to combine the two?

Just FYI, comparisons with `LIKE` are case insensitive in MySQL. `LOWER()` is not needed at all. — fancyPants, Jul 08 '14 at 09:25
@fancyPants I am using **PostgreSQL** and it is very case sensitive. — GingerHead, Jul 08 '14 at 10:35
ILIKE is your friend for case insensitive like in Postgresql — greg, Jul 11 '14 at 08:03

score 2 · Accepted Answer · answered Jul 08 '14 at 08:49

2

Answer given by @dave works fine.

Even this works -

$where->expression("LOWER(title) LIKE ?", '%test%');

answered Jul 08 '14 at 08:49

Kunal Dethe

score 1 · Answer 2 · answered Jul 08 '14 at 00:34

1

Probably not ideal, but you could do literal:

$where->literal("LOWER(foo_name) LIKE '%test%'");

answered Jul 08 '14 at 00:34

dave

2 Answers2