select * from employees
where last_name between 'A' AND 'E';
Why the is answer coming till 'D' not 'E', is there any other way to fetch the details
-
Not exactly, Names between A and E, but start with alphabeticssiva krishna– siva krishna2015-08-26 04:41:21 +00:00Commented Aug 26, 2015 at 4:41
-
String comparison is done on their ASCII values. So, you are comparing the ASCII value of last_name with a single character, which is obviously incorrect.Lalit Kumar B– Lalit Kumar B2015-08-26 04:55:55 +00:00Commented Aug 26, 2015 at 4:55
Add a comment
|
3 Answers
Your command is perfect for "employees whose names are (not start with) from A to E". "E" would be in. "Einstein" is out, as it is later than "E".
WHERE last_name >= 'A' AND last_name < 'F'
will give you what you want.
Alternately, you can do
WHERE SUBSTR(last_name, 1, 1) BETWEEN 'A' AND 'E'
but it would be slow, as it would not be able to use the index.
5 Comments
siva krishna
Thanks for your answer, but my question is why it is not giving till E, Suppose if I give salary between 2000 to 5000 it will fetch till 5000, then why can't for names, what is the string methodology
Amadan
It is giving till E. You just don't have any employees that are called
"E". The question you wanted to ask is "why it is not giving till employees whose names start with E", and I explained that too.Amadan
To piggy-back on your example, if you do
BETWEEN 2000 AND 5000, would you expect it to give you 5000.85? In the same way, "Einstein" is not BETWEEN 'A' AND 'E'.Amadan
Please read again. Your command is equivalent to
BETWEEN 'A' AND 'F' now. If you look at my question, I said < 'F', not <= 'F'. Your command would give you "F" if it was in your table (since 'F' <= 'F'), but not "Franklin", because "Franklin" is not <= 'F'. Open a dictionary and look at in which order the words go; "E" is before every word that starts with "E", "F" is before every word that starts with "F". But every word that starts with "E" is < 'F'.siva krishna
Thanks @ Amadan, I didn't see your second comment, I got it, also with substr(last_name,1,1) between 'A' and 'E';
where last_name between 'A' AND 'E';
String comparison is not similar as comparing numbers.
String comparison is done on their ASCII values. So, you are comparing the ASCII value of last_name with a single character, which will not give your desired output.
SQL> SELECT ename, ASCII(ename), ASCII('A'), ASCII('E') FROM emp;
ENAME ASCII(ENAME) ASCII('A') ASCII('E')
---------- ------------ ---------- ----------
SMITH 83 65 69
ALLEN 65 65 69
WARD 87 65 69
JONES 74 65 69
MARTIN 77 65 69
BLAKE 66 65 69
CLARK 67 65 69
SCOTT 83 65 69
KING 75 65 69
TURNER 84 65 69
ADAMS 65 65 69
JAMES 74 65 69
FORD 70 65 69
MILLER 77 65 69
14 rows selected.
SQL>
Based on above ASCII values, you would get only those rows where you have the ASCII value of the ename between 65 and 69.
You need to use SUBSTR to first extract the first character of last_name and compare it with 'A' and 'E'.
For example, I am using the standard emp table in SCOTT schema and adding two rows with ename starting with 'D' and 'E'.
SQL> WITH DATA AS(
2 SELECT ename FROM emp
3 UNION
4 SELECT 'DAWSON' FROM DUAL
5 UNION
6 SELECT 'EINSTEIN' FROM DUAL
7 )
8 SELECT * FROM DATA
9 WHERE substr(ename,1,1) BETWEEN 'A' AND 'E';
ENAME
----------
ADAMS
ALLEN
BLAKE
CLARK
DAWSON
EINSTEIN
6 rows selected.
4 Comments
Amadan
There is nothing wrong with comparing a multi-character string with a single-character string in and of itself; see my first solution (which is much faster, but equivalent to yours). Also, string comparison is not done on ASCII values, but on collation ordering (which coincide when the collation is
ascii_general_ci).
Lalit Kumar B
@Amadan Where did I say it is wrong to compare a multi-character string to a single-character string? I said OP is doing it incorrectly based on the context. And yes, when you compare two strings, they are compared on their ASCII values.
Amadan
Apologies, it seems I was mistaken. Straight up comparison does not utilise collation.
Lalit Kumar B
@Amadan No problem :-)
SELECT * FROM EMPLOYEES
WHERE Last_name Like 'A%'
OR Last_name Like 'E%';
This should be the answer....The code you have written will show the names which are A and E exactly....
