检查整数的 SQLLike 条件？

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

SELECT * FROM books WHERE title ILIKE "A%"

对于字母来说这是可以的，但是如何列出以任意数字开头的所有项目呢？不管怎样，这是在 Postgres 的尸体上。

200383 次浏览
小开
最佳答案

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;
小开

PostgreSQL supports regular expressions matching.

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

小开

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

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

Or whatever the Postgres equivalent of convert is.

小开

Which one of those is indexable?

This one is definitely btree-indexable:

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

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

小开

In PostreSQL you can use SIMILAR TO operator (more):

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

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

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

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;
小开

Tested on 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]+';
小开

For those who need to find all values that contain a number (regardless of its position) you can just use:

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

(tested with PostgreSQL 13.4)

小开

If you are trying to use like for int column, use CAST to varchar.
(This is Objection RawFunction query example)

.where(
db.obj.raw("CAST(id AS varchar)"),
"like",
`%${data.id}%`
)

提交答案