Hi, I'm working through "Beginning SQL" and running on SQL Server 2005 Express.
The book states that the 'Between' operator is inclusive. However, when I run the SQL query to look for the film titles between 'P' and 'T', films beginning with those letters are not included in the results, although I ran a similar query between 'N' and 'T' and 'films beginning with 'N' were included. This problem seems to be variable in the results returned, although it never seems to include the second item of the query.
As an adjunct to this question: How does 'Between' handle similar queries to the above where the column includes elements starting with a number, as in the film name "15th Late Afternoon", when the column contains text strings?
Any explanations would be gratefully received.

DaveyH

The between operator IS inclusive

I suspect you wrote a query like:

    SELECT *
    FROM yourTable
    WHERE filmTitle BETWEEN 'P' AND 'T'

If that is corret then it is immediately clear why you did not get any films starting with a 'T'. The ordering is the same as in your dictionary, so a 'T' alone comes before (Is maller than) 'T' with anything after it.
To get all files starting with T using the between operator you would say fx:
    WHERE filmTitle BETWEEN 'P' AND 'TZZZZZZZZZZ'
just add enough 'Z'-s to make sure there isnt an actual film with that many.

But the reasoning abowe does not apply to the other end of the betwen range 'P' alone should be the very first of the filmtitles starting with a 'P'.
Could it be that there simply were no films starting with p in your database ?

second question:
   Inside a text string there is no such thing as numbers. just characters. '1' is not the number one, it is a sting containing the digit one. How characters are sorted is detemined by the alfabet used. each alfabet assign a character code to each of its digits. If you look at the alphabet at http://www.asciitable.com/ you will see that the digits from '0' to '9' have character codes from decimal 48 to decimal 57. letters have higher codes (from decimal 65) so they are 'higher' in the ordering.
so:
    WHERE filmTitle BETWEEN '0' AND '9ZZZZZZZZZZ'
should find all filmTitles starting with a number

You should also not that the character code for a capital 'A' (dec 65) is different from the character code for a lovercase 'a' (dec 97). There are a set of collate rules in your database that tells it whether they sould be compared as just 'a's (case independent) or as different characters (case dependent). Above I have assumed case independent collation, because that is the default in modern databases, but your administrator may well have changed it for some reason.

regards JakobA



"good is better than bad case its nicer"  - Mammy Yokum

Thanks for the explanation Jakob. However I'm still not clear as to the handling of numbers that appear in text strings; are you saying that if a number appears at the front of a text string, as in "25 Long Road" the database uses the ascii codes to sort against other text strings that may only contain alphabetic characters?

Regards,

Dave


DaveyH