[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