Convert character to number, like +00005 to 5

We have the fixed format data and it has the number with plus/minus sign, like +00005 or -00005. We’d like to convert it to number but Redshift seems not to allow implicit conversion.

So, I temporarily use to_number(replace(<numbercolumn>, '+', ''),'99999') But I guess there should be more better solutions.

Anyone know more smart way, your advice would be highly appreciated.

Answer

Use cast(colname as datatype):

select cast('-00005' as int)