文本与数值的 SQL BETWEEN [英] SQL BETWEEN for text vs numeric values

问题描述

BETWEEN 在 WHERE 子句中用于选择两个值之间的数据范围.
如果我是正确的，是否排除范围的端点是 DBMS 特定的.
我无法理解以下内容:
如果我有一个值表并执行以下查询:

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;`  

查询返回结果行包括卡路里=33和卡路里=135(即范围端点包括).

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

但如果我这样做:

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

我确实不获取以 O 开头的 food_name 行.IE.范围的末尾被排除.
为了让查询按预期工作，我输入:

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';`   

我的问题是，为什么 BETWEEN 对于数字和文本数据会有这样的差异?

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

推荐答案

Between 对于数字和字符串的操作方式完全相同.两个端点都包含在中.这是 ANSI 标准的一部分，因此它是所有 SQL 方言的工作方式.

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.

表达式:

where num between 33 and 135

num 为 135 时匹配，number 为 135.00001 时不匹配.

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

同理，表达式:

where food_name BETWEEN 'G' AND 'O'

将匹配 'O'，但不匹配任何其他以 'O' 开头的字符串.

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

曾经简单的拼凑就是使用~".这具有最大的 7 位 ASCII 值，因此对于英语应用程序，它通常工作得很好:

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'

不过，重要的一点是 between 的工作方式与数据类型无关.

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

这篇关于文本与数值的 SQL BETWEEN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！