CODEWITHSUNDEEP
sqlpostgresql
ilFonta
ilFonta

Reputation: 301

selecting rows depending on the first digit of an integer in a column

Using SQL in PostgreSQL I need to select all the rows from my table called "crop" when the first digit of the integer numbers in column "field_id" is 7.

select *
from crop
where (left (field_id,1) = 7)

Upvotes: 1

Views: 1134

Answers (2)

Mr.No
Mr.No

Reputation: 83

Try with cast, like this:

select *
from crop
where cast(substring(cast(field_id as varchar(5)),1,1) as int) = 7

where 5 in varchar(5) you should put number how long is your integer.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270061

First, you know that the column is a number, so I would be inclined to explicitly convert it, no matter what you do:

where left(crop::text, 1) = '7'
where crop::text like '7%'

The conversion to text is simply to be explicit about what is happening and it makes it easier for Postgres to parse the query.

More importantly, if the value has a fixed number of digits, then I would suggest using a numeric range; something like this:

where crop >= 700000 and crop < 800000

This makes it easier for Postgres to use an index on the column.

Upvotes: 2

Related Questions