Post reply

LessThanGreaterThan on a string

  • June 24, 2005 at 4:15 am

    #66625

    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

    #568220

    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.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • June 24, 2005 at 5:05 am

    #568221

    Hi Jonathan,

    The datatype of the field "NAME" is nvarchar(250).

    ILANGO

  • June 24, 2005 at 5:12 am

    #568223

    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

    #568227

    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

    #568233

    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

    #568238

    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

    #568258

    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

    #568265

    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