Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > BOOK: Beginning SQL
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning SQL
This is the forum to discuss the Wrox book Beginning SQL by Paul Wilton, John Colby; ISBN: 9780764577321
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning SQL section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 10th, 2007, 04:38 PM
Registered User
 
Join Date: Nov 2005
Location: Epworth, North Lincolnshire, United Kingdom.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL 'Between' operator

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
Reply With Quote
  #2 (permalink)  
Old April 2nd, 2007, 08:23 AM
Registered User
 
Join Date: Apr 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old April 4th, 2007, 04:25 PM
Registered User
 
Join Date: Nov 2005
Location: Epworth, North Lincolnshire, United Kingdom.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to use like operator thillaiarasu SQL Server 2000 4 April 23rd, 2010 10:47 AM
having problem with Between operator anantdev SQL Language 1 May 26th, 2008 09:14 AM
=== operator in c# surendraparashar C# 2005 8 November 8th, 2007 05:14 AM
Invalid operator for data type. Operator equals di Pusstiu SQL Server 2000 2 August 10th, 2007 05:51 AM
SQL "equals operator" in queries Steve777 SQL Language 1 June 24th, 2005 11:42 AM



All times are GMT -4. The time now is 06:41 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.