SQL BETWEEN for text vs numeric values

Question

BETWEEN is used in a WHERE clause to select a range of data between two values.
If I am correct whether the range's endpoint are excluded or not is DBMS specific.
What I can not understand in the following:
If I have a table of values and I do the following query:

SELECT food_name 
    FROM health_foods 
    WHERE calories BETWEEN 33 AND 135;`

The query returns as results rows including calories =33 and calories =135 (i.e. range endpoints are included).

But if I do:

SELECT food_name 
    FROM health_foods 
    WHERE food_name BETWEEN 'G' AND 'O';

I do not get rows with food_name starting with O. I.e. the end of the range is excluded.
For the query to work as expected I type:

SELECT food_name 
    FROM health_foods 
    WHERE food_name BETWEEN 'G' AND 'P';`

My question is why is there such a difference for BETWEEN for numbers and text data?

possible duplicate of SQL Between clause with strings columns — Kermit
– Kermit, Commented Mar 30, 2013 at 16:48

Gordon Linoff · Accepted Answer · 2013-03-30 16:55:16Z

25

Between is operating exactly the same way for numbers and for character strings. The two endpoints are included. This is part of the ANSI standard, so it is how all SQL dialects work.

The expression:

where num between 33 and 135

will match when num is 135. It will not match when number is 135.00001.

Similarly, the expression:

where food_name BETWEEN 'G' AND 'O'

will match 'O', but not any other string beginning with 'O'.

Once simple kludge is to use "~". This has the largest 7-bit ASCII value, so for English-language applications, it usually works well:

where food_name between 'G' and 'O~'

You can also do various other things. Here are two ideas:

where left(food_name, 1) between 'G' and 'O'
where food_name >= 'G' and food_name < 'P'

The important point, though, is that between works the same way regardless of data type.

answered Mar 30, 2013 at 16:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

SteveP · Accepted Answer · 2013-03-30 16:52:48Z

5

Take the example of 'Orange' vs. 'O'. The string 'Orange' is clearly not equal to the string 'O', and as it is longer, it must be greater, not less than.

You could do 'Orange' < 'OZZZZZZZZZZZZZZ' though.

edited Mar 30, 2013 at 16:52

answered Mar 30, 2013 at 16:46

SteveP

19.2k9 gold badges49 silver badges62 bronze badges

3 Comments

Cratylus Over a year ago

I don't understand your answer.WHat do you mean Orange does not equal O?Orange starts with O

SteveP Over a year ago

The string 'Orange' is not the same as the string 'O' because it has extra characters, I.e. it is longer. Only 'O' is equal to 'O'.

Igor Jerosimić Over a year ago

@Cratylus Think of it as decimal places, BETWEEN 33 AND 135 would return 135 but not 135.5

echo_Me · Accepted Answer · 2013-03-30 16:59:27Z

2

try this with REGEX

  WHERE  food_name REGEXP '^[G-O]';

this gives you all food_name wich starts by G till those who starts by O

DEMO HERE

edited Mar 30, 2013 at 16:59

answered Mar 30, 2013 at 16:45

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Collectives™ on Stack Overflow

SQL BETWEEN for text vs numeric values

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related