Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SQL: Use of BETWEEN with alphanumeric ranges

Options
  • 08-02-2008 1:33pm
    #1
    Sleepy
    Registered Users Posts: 24,162 ✭✭✭✭


    I've come across a situation in work that I'm struggling to find anything on Google to help me understand. Some software I'm working on is using a SELECT in MS SQL Server 2005 which should be fairly standard but the range being used is AA000000A to ZZ999999D.

    This has left me with a problem where pretty much any 9 character word meets the select statement. I understand that what the client are really looking for is data mask checking but what I've come to realise is that I haven't a clue how SQL Server handles a between statement when dealing with alphanumeric values.

    If both values are numeric, it's obvious that the returned values are those that are numerically between the provided range values.

    If both range values are purely alpha characters, obviously SQL Server checks for those values that alphabetically fall between these.

    How does it handle a range when the range is defined as an alphanumeric value?

    Any explanation or link to a relevant article would be greatly appreciated, all I can find is basic explanations of the between function.


Comments

  • Options
    #2
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    Sorry, trying to understand this.

    Is it that the first two letters are A-Z, the next 6 are numeric and the last is A-D? Or is it using a "number" range of 0-Z?


  • Options
    #3
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    Sleepy wrote: »
    How does it handle a range when the range is defined as an alphanumeric value?

    I would imagine alphanumeric is treated as purely that - the value of the numbers do not matter, what does matter is their ASCII value.

    This is where the danger lies. Zero is before 1, so if you have an alphanumeric field with A010, it would sort higher than A100, which may not be what you want.

    Note: I'm an Oracle person, so this is based on Oracle experience. I would imagine it is fairly similar for SQL Server.


  • Options
    #4
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭Beano


    Sleepy wrote: »

    If both range values are purely alpha characters, obviously SQL Server checks for those values that alphabetically fall between these.

    How does it handle a range when the range is defined as an alphanumeric value?

    The exact same way it does for purely alpha characters. 0 to 9 are jsut characters as well. Give the range you are using you will pretty much match any other character string that starts with an alpha character.

    What is it you are trying to do? If we know that we could give you more focused answers.


  • Options
    #5
    Sleepy
    Registered Users Posts: 24,162 ✭✭✭✭Sleepy


    Thanks for the replies all, Tom Dunne's answer gave me everything I needed to know - it sorts based on ASCII value.


Advertisement