Rails: Cast string field to integer in where clause

Question

In a postgres database I have a string field code in orders table. The field contains values like 'COA-38-A', 'EFILLDIRT' and 'HE60LS-A'. There is a form on UI to filter orders based on code and only integer values are allowed in the field.

The query should filter the orders by integer values in the code field i.e if 30 is entered, the query should return orders with code 'COA-38-A' and 'HE60LS-A' because the query contains >=

I tried adding ::integer in the query:

Order.where('code::integer >= ?', params[:code])

but got the following error:

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer

Is there any way to filter by only integer values?

regexp_replace(code, '[^0-9]+', '', 'g') should get all digits for you — Vao Tsun
– Vao Tsun, Commented May 24, 2017 at 8:03

Vao Tsun · Accepted Answer · 2017-05-24 09:43:10Z

3

try this?

Order.where("regexp_replace(0||code, '[^0-9]+', '', 'g')::integer >= ?", params[:code].to_i)

edited May 24, 2017 at 9:43

answered May 24, 2017 at 7:57

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

SteveTurczyn Over a year ago

Excellent answer. Is it required to do #to_s on params[:code] ?

Vao Tsun Over a year ago

awful answer - I don't work with rails - I just know how to do it in postgres. And thus Idont know if it should be params[:code].to_s or params[:code] - I used code from OP, but the answer was edited to be with #to_s If to_s stands for to string, I dont think it is needed at all

SteveTurczyn Over a year ago

Yes #to_s is "to string". It's an excellent answer because it solves the OP's problem and you did a creditable job on the syntax. I would suggest params[:code].to_i since in fact the parameter will come in as a string and needs to be substituted as an integer.

Arif Over a year ago

Got this after trying: PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: ""

Vao Tsun Over a year ago

@Arif modified to have leading 0, so empty string would become 0

Collectives™ on Stack Overflow

Rails: Cast string field to integer in where clause

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related