June 24, 2005 at 4:15 am
Hi Pals,
Consider a column: 'name' in table 'employee'
name
-----
Anderson
Bob
Cathy
Derrick
Evan
Ferguson
Gregory
When I execute the following query:
SELECT * FROM EMPLOYEE WHERE [NAME] >='B'
AND [NAME] <='E'
The result *should* have been
name
----
Bob
Cathy
Derrick
Evan
But the resultset was like:
name
----
Bob
Cathy
Derrick
Was my query wrong..?? If so please give me some light on it.
June 24, 2005 at 5:01 am
There is nothing wrong with your query. Have a look to see what data type the column is and post back. May be that you have it as a text type in which case I don't think the query will work.
June 24, 2005 at 5:05 am
Hi Jonathan,
The datatype of the field "NAME" is nvarchar(250).
ILANGO
June 24, 2005 at 5:12 am
Anyways the query :
SELECT * FROM EMPLOYEE WHERE [NAME] >='B'
fetches me records that start from "B" and the rest.
shouldn't the "<=" too work similarly? like:
SELECT * FROM EMPLOYEE WHERE
AND [NAME] <='E'
should get me :
Anderson
Bob
Cathy
Derrick
Evan
but it is missing out "Evan"..
ILANGO
June 24, 2005 at 5:31 am
ILANGO,
The problem is that Evan is > just E. If you want the E's as well have your query do < F. Kind of like dealing with date fields with times....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 24, 2005 at 5:50 am
Hi AJ,
The problem is like from a column of nvarchar datatype, I have to fetch the values as per the user inputted "From" and "To" values.
So the user may key in "Cat" as from and "Eva" as to in the input criteria.
As a result, I should fetch the records : "Cathy", "Derrick" and "Evan"
So I cannot increment the input value on my program.
Incrementing the value would work if, the input is going to be a single character (ASCII value + 1), but thats not the case.
Or tell me if Iam wrong.
ILANGO
June 24, 2005 at 6:07 am
This might do it
DECLARE @From nvarchar(250), @To nvarchar(250)
SET @From = N'Cat'
SET @To = N'Eva'
SELECT *
FROM EMPLOYEE
WHERE LEFT([NAME],LEN(@From)) >= @From
AND LEFT([NAME],LEN(@To)) <= @To
but performance may be poor as it will result in a table scan or index scan (if there is one)
Far away is close at hand in the images of elsewhere.
Anon.
June 24, 2005 at 7:21 am
HI David,
Thanks for the solution. It works gr8 for me.
Can you give me some light on why the "greaterthan and the value" worked and "less than and equal to" didn't.
ILANGO
June 24, 2005 at 7:38 am
It will be the same for any character field.
E with any other subsequent letter is gretaer i.e 'EA' is greater than 'E'
SO obviously 'Evan' is not less than or equel to 'E' If you have just 'E' as name then it will be equel to 'E' and work.
check
SELECT * FROM EMPLOYEE WHERE [Name] >='B'
AND [Name] <= 'EZ'
SELECT * FROM EMPLOYEE WHERE SUBSTRING(Name,1,1) >='B'
AND SUBSTRING(Name,1,1) <= 'E'
will work as you expected
Regards,
gova
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply