Unable to cast redshift column to integer due to empty values

1
votes

I have a redshift table with a column which has empty values rarely. It is expected to have only integer values but some places empty values exist. When I try to cast it using :: it throws error -

[Code: 500310, SQL State: XX000] Amazon Invalid operation: Invalid digit, Value 'B', Pos 0, Type: Integer Details:

error: Invalid digit, Value 'B', Pos 0, Type: Integer code: 1207 context: BEVEL_ON query: 34112149 location: :0 process: query1_836_34112149 [pid=0]

1
castingamazon-redshift

1 Answers

0
votes

So to clarify you have a text column that contains numeric characters most of the time and you want to case this to integer, right? It also sounds like you believe that the only only non-numeric values are the empty string ''.

If this is the case then the solution is fairly simple - change the empty string to NULL before casting. The DECODE statement is my go to for this:

DECODE(col_X, '', NULL, col_X)::INT

If a more varied set of strings are in the column then using regexp_replace() to strip all the non-numeric characters would be needed.