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

Edward J. Sabol sabol at alderaan.gsfc.nasa.gov
Thu Jan 18 16:30:36 GMT 2007

> I've not seen anything to suggest this can be done in CDBI, but I
> thought I'd give it a shot anyway.

Everything that you can do with SQL can be done with CDBI.

> 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.

You're confused. You don't want "SELECT LOWER(column)". You want
"WHERE LOWER(column) = ?".

> Is there any way I could do this directly in Class::DBI?
> I suspect that I'll just be getting the DB handle and constructing my
> own SQL here, but any suggestions are welcome.

You could use set_sql(), but I would probably use add_constructor(), since
it's a little more succinct. Put the following in your CDBI class for the
table that you want to do case-insensitive searches:

  MyCDBIClass->add_constructor(isearch_column => "lower(column) = ?");

Then, in the code, you do

  my @results = MyCDBIClass->isearch_column(lc($column_value));

You can also use Class::DBI::AbstractSearch, like this:

  my @results = MyCDBIClass->search_where(
                   column => [ 'lower(column) = ?', lc($column_value) ]

or something like that.

Hope this helps,

More information about the ClassDBI mailing list