[CDBI] select lower(thing) from...

Gareth Harper gareth at migcan.com
Thu Jan 18 16:37:44 GMT 2007

Oliver Jeeves wrote:

>Howdy folks!
>I've not seen anything to suggest this can be done in CDBI, but I
>thought I'd give it a shot anyway.
>I'm trying to do a case insensitive search on a column in a database.
>I'm using SQLite, which doesn't appear to use ILIKE, so the recipe from
>the cookbook for case insensitive searches won't work.
>I would imagine that the best way to achieve this, it to convert my
>search term to lower case, and SELECT LOWER(column) from the database
>for the comparrison. Is there any way I could do this directly in
>I suspect that I'll just be getting the DB handle and constructing my
>own SQL here, but any suggestions are welcome.
Use set_sql.  You'll still have to write the SQL yourself, however once 
it's done you can always run


As below:

    current_services => qq{
            table2.field4 as field4
        FROM table1,table2
        WHERE table1.field  = ?
        AND table2.id = table1.table2_id

And to invoke:

my $objects = My::Package->search_current_services($params);

This is a slightly more complex exampel as it used temporary field names 
in the query:

My::Package->columns(TEMP => qw/field4/);

Hope that helps

More information about the ClassDBI mailing list