[Dbix-class] DBIx::Class w/SQLite and COLLATE RTRIM

Toby Corkindale toby.corkindale at strategicdata.com.au
Thu Oct 14 01:21:17 GMT 2010


Background knowledge:
SQLite breaks from SQL traditions by not ignoring trailing whitespace 
when comparing strings.
ie. SELECT 'FOO ' = 'FOO'; # returns false
However you can specify that it trim the whitespace and DWIW like this:
SELECT 'FOO ' = 'FOO' COLLATE RTRIM; # returns true

So, now I want to apply that to queries in DBIx::Class.
Currently I can do:
my $rs = $schema->resultset('Foo');
$rs->search({ name => 'bar' })->count;
# returns 0
$rs->search({ name => 'bar  ' })->count;
# returns some

One option I have is this:
$rs->search({ name => \" = 'bar' COLLATE RTRIM" })->count;

But that is far, far from making me happy.

I wondered if others have encountered this issue, and if so, have you 
found a neater solution?

Toby (wintrmute)

More information about the DBIx-Class mailing list