unable to make out use of BETWEEN in oracle
I am not able to make this out: Between eliminates use of开发者_JAVA技巧 >= 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?
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:
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.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.
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?
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;
You seem to have used a lowercase 'p' in the top query and an uppercase 'P' in the second. Was this intentional?
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?
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")
精彩评论