SQL LIKE 条件来检查整数？

SQL LIKE condition to check for integer?

提问by Wayne Koorts

I am using a set of SQL LIKE conditions to go through the alphabet and list all items beginning with the appropriate letter, e.g. to get all books where the title starts with the letter "A":

我正在使用一组 SQL LIKE 条件来浏览字母表并列出以适当字母开头的所有项目，例如获取标题以字母“A”开头的所有书籍：

SELECT * FROM books WHERE title ILIKE "A%"

That's fine for letters, but how do I list all items starting with any number? For what it's worth this is on a Postgres DB.

这对字母没问题，但如何列出以任何数字开头的所有项目？值得一提的是 Postgres 数据库。

回答by Johannes Weiss

That will select (by a regex) every book which has a title starting with a number, is that what you want?

这将选择（通过正则表达式）每本书的标题以数字开头，这是您想要的吗？

SELECT * FROM books WHERE title ~ '^[0-9]'

if you want integers which start with specific digits, you could use:

如果你想要以特定数字开头的整数，你可以使用：

SELECT * FROM books WHERE CAST(price AS TEXT) LIKE '123%'

or use (if all your numbers have the same number of digits (a constraint would be useful then))

或使用（如果您所有的数字都具有相同的位数（那么约束会很有用））

SELECT * FROM books WHERE price BETWEEN 123000 AND 123999;

回答by Vinko Vrsalovic

PostgreSQL supports regular expressions matching.

PostgreSQL 支持正则表达式匹配.

So, your example would look like

所以，你的例子看起来像

SELECT * FROM books WHERE title ~ '^\d+ ?' 

This will match a title starting with one or more digits and an optional space

这将匹配以一个或多个数字开头的标题和一个可选的空格

回答by gellezzz

If you want to search as string, you can cast to text like this:

如果要搜索为字符串，可以像这样转换为文本：

SELECT * FROM books WHERE price::TEXT LIKE '123%'

回答by skensell

I'm late to the party here, but if you're dealing with integers of a fixed length you can just do integer comparison:

我来晚了，但如果你处理固定长度的整数，你可以做整数比较：

SELECT * FROM books WHERE price > 89999 AND price < 90100;

回答by u5827450

Tested on PostgreSQL 9.5 :

在 PostgreSQL 9.5 上测试：

-- only digits

-- 只有数字

select * from books where title ~ '^[0-9]*$';

or,

或者，

select * from books where title SIMILAR TO '[0-9]*';

-- start with digit

-- 以数字开头

select * from books where title ~ '^[0-9]+';

回答by Corey Porter

Assuming that you're looking for "numbers that start with 7" rather than "strings that start with 7," maybe something like

假设您正在寻找“以 7 开头的数字”而不是“以 7 开头的字符串”，可能类似于

select * from books where convert(char(32), book_id) like '7%'

Or whatever the Postgres equivalent of convert is.

或者任何 Postgres 等价物的 convert 是什么。

回答by peufeu

Which one of those is indexable?

其中哪一个是可索引的？

This one is definitely btree-indexable:

这个绝对是 btree 可索引的：

WHERE title >= '0' AND title < ':'

Note that ':' comes after '9' in ASCII.

请注意，':' 出现在 ASCII 中的 '9' 之后。

回答by Má?a - Stitod.cz

In PostreSQL you can use SIMILAR TOoperator (more):

在 PostreSQL 中，您可以使用SIMILAR TO运算符（更多）：

-- only digits
select * from books where title similar to '^[0-9]*$';
-- start with digit
select * from books where title similar to '^[0-9]%$';