LIKE search operator with numerical fields

Questions and answers regarding general SQL and backend databases

LIKE search operator with numerical fields

Postby rossent » Wed May 08, 2013 5:42 pm

Hi all,

We have several cases where we need to be able to perform searches in the database using the LIKE operator on numerical/integer columns.

For example, the SQL statement below displays any employee whose salary ends with '500':

Code: Select all
SELECT * FROM EMPLOYEE WHERE SALARY LIKE '%500'


Most databases support such searches by implicitly converting the numeric column to characters and then performing the actual pattern matching. Unfortunately, Servoy does not support that. It is even more troubling that sometimes servoy simply ignores the specified search criteria or changes it to something else. This applies when using the foundset.find() -> foundset.search() method.

For example, the code below (the logical operation is "city is London and salary contains 500") will ignore completely the salary search criteria and the resulting SQL will include only the "...where city = 'London' ":

Code: Select all
if(foundset.find())
{
    city = 'London'
    salary = '%500%';
    foundset.search();
}


The example below (the logical operation is "city is London and salary starts with 5") will change criteria and the SQL will be "...where city = London and salary = 5 ":
Code: Select all
if(foundset.find())
{
    salary = '5%';
    foundset.search();
}


And if you try to do something like this using the QBSelect approach, you will get an exception stating something like "... %500% cannot be converted to numeric value..." or similar - basically it is trying to convert the search value for the LIKE operator to the data type of the column for which it is applied.

As far as I am concerned, the first two examples are bugs - Servoy should either generate the correct SQL statement which was requested or through an exception stating that something is not supported. Silently changing or ignoring the search criteria is the worst option and should be fixed.

The exception thrown by the QBSelect is better compared to the prior two, but the question is - why does it fail when the databases which we use can handle perfectly well such searches?

We would like to keep the database-agnostic approach for which Servoy is famous and not resolve to using raw SQL - how can we achieve our requirements?
Rossen Totev
Argos Software
rossent
 
Posts: 288
Joined: Wed Dec 31, 2008 2:03 pm

Re: LIKE search operator with numerical fields

Postby rossent » Wed May 08, 2013 5:49 pm

A quick search on StackOverflow reveals that even the underlying Hibernate supports such queries:

http://stackoverflow.com/questions/1902 ... e-operator

So, the question is - why Servoy does not?
Rossen Totev
Argos Software
rossent
 
Posts: 288
Joined: Wed Dec 31, 2008 2:03 pm

Re: LIKE search operator with numerical fields

Postby Hans Nieuwenhuis » Wed May 08, 2013 9:10 pm

+1
I use Oracle and like to do a like on a number/integer column too
Hans Nieuwenhuis
Betagraphics
http://www.deltics.nl
http://www.betagraphics.nl

Servoy Version 7.3.1
Java version 1.7.0.x
Database Oracle 11g
User avatar
Hans Nieuwenhuis
 
Posts: 1026
Joined: Thu Apr 12, 2007 12:36 pm
Location: Hengelo, The Netherlands

Re: LIKE search operator with numerical fields

Postby rossent » Wed May 08, 2013 11:35 pm

I created a case for that which you can vote for: https://support.servoy.com/browse/SVY-4500
Rossen Totev
Argos Software
rossent
 
Posts: 288
Joined: Wed Dec 31, 2008 2:03 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 4 guests

cron