QueryBuilder case sensitivity

Classic Servoy
1

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

2

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

var myValue = 'foobar';
query.where.add(query.columns.my_column.upper.eq(myValue.toUpperCase()));
3

Thanks Sean,

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

4

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?

5

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.