Page 1 of 1

QueryBuilder case sensitivity

PostPosted: Tue Feb 13, 2018 5:43 pm
by steve1376656734
I am trying to do a case-insensitive search using query builder.

I know that if I use a table filter I can prefix my search value with a '#' to make it case insensitive so is there something similar for the query builder syntax?

I believe that hibernate has support for case-insensitive searches using the "ilike" operator but this doesn't seem to have been implemented in query-builder.

Thanks
Steve

Re: QueryBuilder case sensitivity

PostPosted: Tue Feb 13, 2018 7:03 pm
by sean
Hi Steve,

It's easily supported.
In find/relations, the '#' operator is shorthand, but in SQL you essentially have to switch both sides of the comparison to upper or lower.

In query builder, use the upper function of QBColumn
For javascript strings, use toUpperCase() function

Code: Select all
var myValue = 'foobar';
query.where.add(query.columns.my_column.upper.eq(myValue.toUpperCase()));

Re: QueryBuilder case sensitivity

PostPosted: Tue Feb 13, 2018 7:09 pm
by steve1376656734
Thanks Sean,

Is it worth me submitting a feature request for query builder to support the "ilike" operator from hibernate?

Re: QueryBuilder case sensitivity

PostPosted: Tue Feb 13, 2018 7:24 pm
by sean
Hi Steve,

No. ILIKE is not part of the SQL standard and is a vendor specific extension.
The QB API, and Servoy in general, is oriented around ANSI SQL standards, so that we can support many database vendors.

Is there a reason why the provided example doesn't work for you?

Re: QueryBuilder case sensitivity

PostPosted: Wed Feb 14, 2018 5:54 pm
by steve1376656734
The example works fine but I think that using ilike is more intuitive and as Servoy uses hibernate under the hood (I believe) then I thought that implementing that in the query builder would be relatively straightforward.