SQL Between子句与字符串列 [英] SQL Between clause with strings columns

问题描述

我想在字符串列之间使用betweenclausule进行搜索。做一些测试，我得到这个：

I want to make a search using "between" clausule over a string column. Doing some test I got this:

假设有一个国家表有一个name列的类型varchar。如果执行此查询：

Lets asume that there is a country table with a "name" column of type varchar. If a execute this query:

Select * from country where name between 'a' and 'b'

我得到这个结果：

Argentina
.
.
.
Argelia.

它排除了以B开头的那些国家，我发现有点奇怪。

It excludes those countries that starts with B which I found a little bit weird.

有没有办法以更准确的方式进行这项搜索？

Is there a way to do this search in a more accurate way? Any other ideas for make this search?

提前感谢

推荐答案

表达式

name between 'A' and 'B'

等效于

name>='A' and name<='B'

So'Argentina'is> ='A'and< ='B '并且它满足条件。但玻利维亚不是< ='B'。 '玻利维亚'>'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'.

要完成你想要的，你可以这样说：

To accomplish what you want, you could say:

substr(name,1,1) between 'A' and 'B'

或：

name like 'A%' or name like 'B%'

$ b b

或：

or:

name>='A' and name<'C'

这篇关于SQL Between子句与字符串列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！