0

Consider this table named easy_drinks,

+------------------+ | drink_name | +------------------+ | Kiss on the Lips | | Hot Gold | | Lone Tree | | Greyhound | | Indian Summer | | Bull Frog | | Soda and It | | Blackthorn | | Blue Moon | | Oh My Gosh | | Lime Fizz | +------------------+

A query as such,

 select drink_name from easy_drinks where drink_name BETWEEN 'G' and 'O';

results in 

+------------------+ | drink_name | +------------------+ | Kiss on the Lips | | Hot Gold | | Lone Tree | | Greyhound | | Indian Summer | | Lime Fizz | +------------------+

drink names starting with O are not included in the result. But as per the manual page

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN >returns 1, otherwise it returns 0.

Why is the query giving such results?

I have been through questions that explain the behaviour for Timestamp and Date. What is the reason in this case?

Improve this question

2 Answers 2

Reset to default
2

tl;dr

Don't use BETWEEN for strings

where drink_name >= 'G' and drink_name < 'P';

Why?

The O is effectivly expanded with trailing spaces to match the column. So

'O '

is before

'Oh My Gosh'

So you'd need

where drink_name BETWEEN 'G' and 'OZ';

If you have a drink called Ozymandias then this won't work. So:

where drink_name BETWEEN 'G' and 'OZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ';

But, can we can safely assume that there is no drink called P and many spaces?
This is not understandable.

select drink_name from easy_drinks where drink_name BETWEEN 'G' and 'P';

The obvious choice might be to compare only first letters using LEFT

select drink_name from easy_drinks where LEFT(drink_name, 1) BETWEEN 'G' and 'O';

But this will prevent used of any index on drink_name.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Also where drink_name >= 'G' and drink_name < 'P'
@ypercube: was going to say that but got carried away :)
What I'm not sure is how case sensitivity affects these variations.
That would be a collation issue. I work case insensitive almost always
2

Here's an easier way to spot what's going on:

select drink_name, drink_name >='G', drink_name <='O' from easy_drinks order by 1;

Key results are:

  • Greyhound is >= 'G'
  • Oh My Gosh is not <= 'O'

It makes sense once you think about it. In a dictionary, a single O entry is always the first item in letter O.

You probably want something like this:

select drink_name from easy_drinks where SUBSTRING(drink_name, 1, 1) BETWEEN 'G' and 'O';

Edit:

I completely forgot about LEFT(drink_name, 1), which is a handy shorcut for SUBSTRING(drink_name, 1, 1).

Improve this answer

1 Comment

LEFT also means "don't use any index" though.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.