unable to make out use of BETWEEN in oracle

Question

I am not able to make this out: Between eliminates use of >= and <=

...but when i tried this query:

SELECT *  
  FROM names 
 WHERE name >= 'Ankit' 
   AND name <= 'P'

...it gives output:

name
------
Ankit
Mac
Bob

When I tried:

SELECT *  
  FROM names  
 WHERE name BETWEEN 'Ankit' AND 'P'

...it gives output:

name
------
Ankit

Can you explain this why?

Answer 1

You seem to have used a lowercase 'p' in the top query and an uppercase 'P' in the second. Was this intentional?

Answer 2

SELECT *  
FROM names  
WHERE name BETWEEN 'Ankit' AND 'P'

also should return all three rows - I just verified that it does so using your example. Are you sure that you made the test correctly? Maybe you inserted data in other session and didn't commit additional rows?

Answer 3

I'm quite certain this has nothing to do with your syntax and everything to do with your DB setup. I've recreated your test scenario and, like others, have no problem with either query returning the results you expect. Did you check your NLS_SESSION_PARAMETERS as mentioned earlier?

Answer 4

SQL code is case insensitive. String values and string comparisons are case sensitive.

See for yourself:

SELECT CASE WHEN 'a' = 'A' THEN 'string comparison is case insensitive'
            WHEN 'a' <> 'A' THEN 'string comparison is case sensitive'
       END 
FROM dual;

Answer 5

Nut 100% sure about it, but as it's only 3 rows, you could try it:

SELECT *  
  FROM names  
 WHERE (name BETWEEN "Ankit" AND "P")
OR (name BETWEEN "ankit" AND "p")

Answer 6

First, Oracle VARCHAR2 type is case sensitive.

Second, check that you do not have spaces in the beginning of name like this:

" Bob"
" Mac"

Use trim function to check if this causes the problem:

SELECT *  
FROM names  
WHERE trim(name) BETWEEN 'Ankit' AND 'P'

If this does not help, check that language and sort order are correct for your database.

Edit:
Since above advice did not solve your problem, you could try following:

1. Maybe you have some other non-printable characters in field. Use Oracle DUMP function to check:

   SELECT DUMP(name), name FROM names
   

   You should get something like this:

   Typ=1 Len=3: 66,111,98   Bob
   ...
   

   Verify that Len is correct length.

2. Check NLS parameters so that they are not inadvertently changed to something that does not work for your database:

   SELECT * FROM NLS_SESSION_PARAMETERS
   SELECT * FROM NLS_DATABASE_PARAMETERS
   SELECT * FROM NLS_INSTANCE_PARAMETERS
   

   Check results of these three queries and verify that parameters on sort, language and character set are correct.