带有字符串列的 SQL 之间的子句 [英] SQL Between clause with strings columns

问题描述

我想在字符串列上使用"between"子句进行搜索.做一些测试我得到了这个:

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

让我们假设有一个带有 varchar 类型的名称"列的国家/地区表.如果我执行此查询:

Let's assume that there is a country table with a "name" column of type varchar. If I 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'

所以 'Argentina' 是 >='A' 和 <='B' 并且它满足条件.但是玻利维亚"不是<='B'.'玻利维亚'>'B'.它不仅仅查看第一个字母:它查看整个字符串.这肯定是它应该的样子:如果它不这样做，就无法说您想要一个包含Smith"但不包含Smithers"的范围.

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%'

或:

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

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