Search code examples
stringpostgresqlcasting

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.

Solution

  • 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;