CODEWITHSUNDEEP
stringpostgresqlcasting
Dharmendra Kumar Yadav
Dharmendra Kumar Yadav

Reputation: 13

How to cast String into int in PostgreSql

I want to cast string into Integer. I have a table like this.

Have:

ID     Salary
1      "$1,000"
2      "$2,000"

Want:

ID   Salary
1    1000
2    2000

My query

Select Id, cast(substring(Salary,2, length(salary)) as int) 
from have

I am getting error.

ERROR: invalid input syntax for type integer: "1,000"
SQL state: 22P02

Can anyone please provide some guidance on this.

Upvotes: 1

Views: 7887

Answers (1)

user330315
user330315

Reputation:

Remove all non-digit characters, then you cast it to an integer:

regexp_replace(salary, '[^0-9]+', '', 'g')::int

But instead of trying to convert the value every time you select it, fix your database design and convert the column to a proper integer. Never store numbers in text columns.

alter table bad_design 
     alter salary type int using regexp_replace(salary, '[^0-9]+', '', 'g')::int;

Upvotes: 2

Related Questions