QueryBuilder case sensitivity

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

QueryBuilder case sensitivity

Postby steve1376656734 » Tue Feb 13, 2018 5:43 pm

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
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: QueryBuilder case sensitivity

Postby sean » Tue Feb 13, 2018 7:03 pm

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()));
Software Engineer
Servoy USA
sean
 
Posts: 370
Joined: Mon May 21, 2007 6:26 pm
Location: USA

Re: QueryBuilder case sensitivity

Postby steve1376656734 » Tue Feb 13, 2018 7:09 pm

Thanks Sean,

Is it worth me submitting a feature request for query builder to support the "ilike" operator from hibernate?
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK

Re: QueryBuilder case sensitivity

Postby sean » Tue Feb 13, 2018 7:24 pm

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?
Software Engineer
Servoy USA
sean
 
Posts: 370
Joined: Mon May 21, 2007 6:26 pm
Location: USA

Re: QueryBuilder case sensitivity

Postby steve1376656734 » Wed Feb 14, 2018 5:54 pm

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.
Steve
SAN Developer
There are 10 types of people in the world - those that understand binary and those that don't
steve1376656734
 
Posts: 327
Joined: Fri Aug 16, 2013 2:38 pm
Location: Ashford, UK


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 11 guests