Between ordered values

Question

I have a table of employees and a program that displays some query criteria that the user can use for the SELECT, for example, an user can search for an employee (or a set of them) by using the employee ID, however, I let the user select a range of IDs to make the search.

In this implementation the DB holds string values for the ID, they look like this:

'000001','000002'....,'000200' and so on, whilst being strings they are also numbers. So when I make a query like this:

select * from  employees where id_employee BETWEEN '000001' AND '000056'

I get the results I would expect, but when I do this:

select * from  empleados where id_empleado BETWEEN '000056' AND '000001'

I get nothing, it would seem as if SQLServer is treating the valus as Integers or Decimal values and thus not being able to get the results.

I could make a validation in the criteria window I use, but I was wondering if there was a way to make it default for the DB or anything that would avoid me having to change about 100 of this criteria windows.

Thanks in advance.

EDIT: I am not arguing the functionality of the BETWEEN operator, but rather looking for a way to still use it by telling SQLServer to do it the way I should be using it??

Answer 1

It is a bit odd to me that you would want to store your FK in form you've displayed here. To make this work I would suggest that you use integer value for your FK's and then simply do a query like this:

select * from  empleados where id_empleado BETWEEN 56 AND 1

Answer 2

Even if you use numeric values the result is the same. SQL translates between v1 and v2 as >= v1 and <= v2.

declare @emp table (id varchar(10), id2 int);
insert into @emp values
('0001',1),
('0002',2),
('0010',10),
('0020',20),
('0030',30),
('0040',40);

select * from @emp where id between '0010' and '0030'
select * from @emp where id between '0030' and '0010'

select * from @emp where id2 between 10 and 30
select * from @emp where id2 between 30 and 10

You could cast it to int:

select * from @emp where cast(id as int) between 10 and 30 

But the result is the same.

Check it here: http://rextester.com/IDYU18650

Answer 3

BETWEEN is always going to expect <smaller value> AND <larger value>. When the values are reversed (<larger value> AND <smaller value>), you run into trouble.

If you are using parameterized queries (highly recommended), then you have a criteria window which passes the user's choices into the query as parameters, which means your query looks like this:

SELECT * from employees WHERE id_employee BETWEEN :Parameter1 AND :Parameter2

If this is the case, then you can hope to solve the problem by altering your query to run a CASE comparison on the parameters, check which one is bigger and which one is smaller, and thus solve the issue:

SELECT * 
FROM employees
WHERE id_employee
    BETWEEN 
    CASE 
        WHEN :Parameter1 >= :Parameter2 
            THEN :Parameter2 
        ELSE :Parameter1 
    END 
    AND 
    CASE
        WHEN :Parameter2 <= :Parameter1
            THEN :Parameter1
        ELSE :Parameter2
    END
;

This is just one example of how you could write this, but I think you'll get the idea. In this query, it doesn't matter if you write :Parameter1 = '00056' and :Parameter2 = '00001', or vice-versa; either way, the result returned will be identical.

If every single criteria window is using the same basic logic, then you probably won't be able to avoid changing this in each one.

This is, however, a good example of how using centralized queries (like Stored Procedures, for example) could help save you work. If all of your criteria windows were executing a stored procedure or similar saved query, you could just update that one central query and all of the windows would start working correctly.

But if each window is running its own dynamic query, you won't have any option but to go window-by-window and change this functionality.

Answer 4

How about:

select * from  employees where (id_employee BETWEEN '000001' AND '000056') or (id_employee BETWEEN '000056' AND '000001')

In Powerbuilder language with the two parm lim1 and lim2 (defined as string):

select * from  employees where (id_employee BETWEEN :lim1 AND :lim2) or (id_employee BETWEEN :lim2 AND :lim1)