Different behavior of 'between' operator in oracle

Question

I know ' between' operator is inclusive for ranges specified..but in following case it works differently

I have table customer with following attributes.

  customer 
    {
     customername  varchar2(30),
      custid  integer(10,0)

    }

Query

   select *  from customer c where  c.customername between  'a'  and 'b';

Above query fetches data only customer name starting with 'a'. But when we use 'between' operator with number in that case both are inclusive.Could anyone explain me this bahvior.

Answer 1

It's not different. BETWEEN is still inclusive. However you should keep in mind that string comparison in programming is a little bit more complicated than integer comparison. In the database strings are ordered like this:

'a' < 'andy' < 'andy1' < 'anna' < 'b' < 'boris' < 'brian'.

So between 'a' and 'b' will return:

'a', 'andy', 'andy1', 'anna', 'b' 

But since you don't have a customer named exactly 'b' you get only:

'a', 'andy', 'andy1', 'anna' 

If you want only those customers whose name starts with a or b you should use SUBSTR:

select *  from customer c where  SUBSTR(c.customername, 1, 1) between  'a'  and 'b';

Answer 2

The BETWEEN operator acts as:

And the value of

expr1 BETWEEN expr2 AND expr3

is the value of the boolean expression:

expr2 <= expr1 AND expr1 <= expr3

It works the same way for character strings as for numbers, but you have to think about it a bit more for strings.

In particular:

 'a'  <= 'b' // true
 'ab' <= 'b' // true
 'b'  <= 'b' // true
 'ba' <= 'b' // FALSE

If you had a customer with the name exactly b in your dataset, it would be returned by your between query. But bob wouldn't be returned, because 'bob' <= 'b' is false.