Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > BOOK: Beginning SQL
Password Reminder
| FAQ | Members List | 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Thread Tools Search this Thread 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.

  #2 (permalink)  
Old April 2nd, 2007, 07:23 AM
Registered User
Join Date: Apr 2007
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

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

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?




Thread Tools Search this Thread
Search this Thread:

Advanced Search
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 09:47 AM
having problem with Between operator anantdev SQL Language 1 May 26th, 2008 08: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 04:51 AM
SQL "equals operator" in queries Steve777 SQL Language 1 June 24th, 2005 10:42 AM

All times are GMT -4. The time now is 03:02 AM.

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