Range query

[Rathtap]

This query returns only those starting with A and B. Why?
SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'

[Hugo Kornelis]

On 18 Aug 2004 07:19:13 -0700, Rathtap wrote:

This query returns only those starting with A and B. Why?
SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'

Hi Rathap,

Because the name 'Chris' is considered to be greater than 'C'. If the
first character is equal, comparison looks at the second, etc; for this
purpose, the shorter name is padded with spaces. So the comparison of
'Chris' to 'C' in detail: 'C" gets padded to 'C '; the first characters
(both 'C') are equal; 'h' is compared to ' ' and is greater.

Remember that myname BETWEEN 'A' AND 'C' is shorthand for myname >= 'A'
AND myname <= 'C', so all names beginning with A or B will be selected, as
well as names exactly equal to 'C'.

To get all names beginnen with A, B and C, use BETWEEN 'A' AND 'CZZZZZZZ'
(and check how numbers and special characters sort in your collation), use
myname >= 'A' and myname < 'D', or use myname LIKE '[ABC]%'.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Rowland]

"Rathtap" <am****@yahoo.com> wrote in message
news:b2**************************@posting.google.c om...

This query returns only those starting with A and B. Why?
SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'

The first value is inclusive while the second value is exclusive in a
BETWEEN expression.

You could try :
WHERE name >= 'A'
AND name <= 'C'

Or :
BETWEEN 'A' AND 'D'

Depending on what you were trying to achieve.

[Ross Presser]

On Wed, 18 Aug 2004 15:38:13 +0000 (UTC), Rowland wrote:

"Rathtap" <am****@yahoo.com> wrote in message
news:b2**************************@posting.google.c om...

This query returns only those starting with A and B. Why?
SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'

The first value is inclusive while the second value is exclusive in a
BETWEEN expression.

This is wrong. The second value is inclusive; but names that *start* with
C are not included because they are not *between* A and C.

This means that if there is a plain value 'C' in your table, it will be
included, but 'CALLIE' will not, because 'CALLIE' > 'C'

(Hugo Kornelis already explained this, but I wanted to follow up this
incorrect impression.)

You can use BETWEEN 'A' AND 'CZZZZZZZ', or LIKE '[ABC]%' as Hugo suggested,
or you can use WHERE LEFT(myname,1) BETWEEN 'A' AND 'C'. I suspect that the
first alternative is most efficient.