带有字符串列的 SQL 之间的子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5980783/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Between clause with strings columns
提问by Cheluis
I want to make a search using "between" clause over a string column. Doing some test I got this:
我想在字符串列上使用“之间”子句进行搜索。做一些测试我得到了这个：
Let's assume that there is a country table with a "name" column of type varchar. If I execute this query:
让我们假设有一个 country 表，其中包含一个 varchar 类型的“name”列。如果我执行此查询：
Select * from country where name between 'a' and 'b'
I got this result:
我得到了这个结果：
Argentina
.
.
.
Argelia.
It excludes those countries that starts with B which I found a little bit weird.
它排除了那些我觉得有点奇怪的以 B 开头的国家。
Is there a way to do this search in a more accurate way? Any other ideas for make this search?
有没有办法以更准确的方式进行此搜索？进行此搜索的任何其他想法？
Thanks in advance
提前致谢
回答by Jay
The expression
表达方式
name between 'A' and 'B'
is equivalent to
相当于
name>='A' and name<='B'
So 'Argentina' is >='A' and <='B' and it satisfies the condition. But 'Bolivia' is NOT <='B'. 'Bolivia'>'B'. It doesn't just look at the first letter: it looks at the whole string. Which is surely the way it ought to be: if it didn't do this, there'd be no way to say that you wanted a range that included 'Smith' but not 'Smithers'.
所以 'Argentina' 是 >='A' 和 <='B' 并且它满足条件。但“玻利维亚”不是 <='B'。'玻利维亚'>'B'。它不仅仅查看第一个字母：它查看整个字符串。这肯定是它应该的方式：如果它不这样做，就无法说您想要一个包含“Smith”但不包含“Smithers”的范围。
To accomplish what you want, you could say:
为了完成你想要的，你可以说：
substr(name,1,1) between 'A' and 'B'
or:
或者：
name like 'A%' or name like 'B%'
or:
或者：
name>='A' and name<'C'
回答by Ariwibawa
i think i know how to solve your problem. u can try adding extra character in the back like this
我想我知道如何解决你的问题。你可以尝试像这样在后面添加额外的字符
select * from tablea where column1 between 'ABC' and 'ACD'+'Z'
this will return a result from ABC% to ACE
这会将结果从 ABC% 返回到 ACE
回答by Blindy
The result's accurate, but you may be misunderstanding. x between a and bmeans x>=a and x<=b. (See the PostGRES documentationfor details.)
结果是准确的，但您可能会误解。x between a and b是指x>=a and x<=b。（有关详细信息，请参阅PostGRES 文档。）
If you want to get lines that start with either an aor a b, say what you mean:
如果您想获得以 ana或 a开头的行b，请说出您的意思：
select * from country where name like 'a%' or name like 'b%'
likeuses the table indices so you'll get full performance for using this.
like使用表索引，因此您将获得使用它的全部性能。
回答by Daniel Corbett
Another query that would get countries that start with b as well as A, would be:
获得以 b 和 A 开头的国家的另一个查询是：
Select * from country where name between 'a' and 'c'
回答by David Chan
Select * from country where substring(name FROM 1 FOR 1) between 'A' and 'B';
回答by bluewind
The reason this statement didn't work is SQL pads the string with whitespace until it's the same length as the comparing string. So, in comparison, sql compared bfollowed by several blanks with b******. Because whitespace appears before all the other letters, sql decided b*****came after b[6 spaces]. So it shouldn't be displayed.
该语句不起作用的原因是 SQL 用空格填充字符串，直到它与比较字符串的长度相同。因此，相比之下，sql 比较b后跟几个空格b******。因为空格出现在所有其他字母之前，所以 sql 决定b*****在b[6 spaces]. 所以不应该显示。
