文字与数字值之间的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?

推荐答案

数字和字符串之间的操作方式完全相同.包括两个端点 .这是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屋！