This query returns only those starting with A and B. Why?
SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'
SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'
-
-
Re: Range query
On 18 Aug 2004 07:19:13 -0700, Rathtap wrote:
[color=blue]
>This query returns only those starting with A and B. Why?
>SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'[/color]
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)
-
Re: Range query
"Rathtap" <amcniw@yahoo.c om> wrote in message
news:b21a5958.0 408180619.7d33b fc2@posting.goo gle.com...[color=blue]
> This query returns only those starting with A and B. Why?
> SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'[/color]
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.
Comment
-
Re: Range query
On Wed, 18 Aug 2004 15:38:13 +0000 (UTC), Rowland wrote:
[color=blue]
> "Rathtap" <amcniw@yahoo.c om> wrote in message
> news:b21a5958.0 408180619.7d33b fc2@posting.goo gle.com...[color=green]
>> This query returns only those starting with A and B. Why?
>> SELECT name FROM mytable WHERE name BETWEEN 'A' AND 'C'[/color]
>
> The first value is inclusive while the second value is exclusive in a
> BETWEEN expression.[/color]
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.
Comment
Comment