选择范围中的第一个字母( PostgreSQL ) [英] Select where first letter in a range ( PostgreSQL )

问题描述

我试图从一个表中选择单词的第一个字母在一个范围内(例如 a-f)

I am trying to select from a table where word's first letter is in a range (a-f for example)

我尝试过这样的 where 子句:

I tried with a where clause like this:

WHERE lower(substring(title from 1 for 1)) IN ARRAY['a', 'k', 't']

WHERE lower(substring(title from 1 for 1)) IN ARRAY['a', 'k', 't']

希望以后能找到动态生成范围ARRAY的方法.

hoping that I will find a way to generate the range ARRAY dynamically later.

以上方法无效.知道我做错了什么吗?

The above is not working. Any idea what I'm doing wrong?

推荐答案

您可以使用 SIMILARTO 关键字.以下将匹配以a"、k"或t"开头的所有标题.

You can use the SIMILAR TO keyword. The following will match all titles that start with either 'a', 'k', or 't'.

... WHERE lower(title) SIMILAR TO '(a|k|t)%'

如果你想使用一个范围，你可以使用 [] 表示法:

If you want to use a range, you could use the [] notation:

... WHERE lower(title) SIMILAR TO '[a-f]%'

注意事项

1. % 字符匹配模式后面的任意数量的字符.例如，第二个模式示例将匹配:'abc'、'ab'、'a'、'far'、'fear' 等

1. The % character matches any number of characters following the pattern. For instance, the second pattern example would match: 'abc', 'ab', 'a', 'far', 'fear' etc.

另外，需要注意的是 SIMILAR TO 关键字仅适用于 PostgreSQL，而不是 ANSI SQL.

Also, it is important to note that the SIMILAR TO keyword is only available to PostgreSQL and it is not ANSI SQL.

最后，lower(title) 在使用字符类时不是必需的.你可以简单地搜索类似

Finally, the lower(title) is not necessary when using the character class. You could simply search for something like

WHERE title SIMILAR TO '[a-fA-F]%'

这篇关于选择范围中的第一个字母( PostgreSQL )的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！