Safe cast from string to integer

Hello,

I have a column of type VARCHAR, storing some generally character data.
A few of the values in that column are in fact numbers represented as
strings (e.g., "12345").

At some moment I need to select those numbers, cast them to INT and do
some math on them. Naturally, I do something like this:

SELECT INT(MYCOLUMN) WHERE I_BELIEVE_THIS_IS_A_NUMBER='true'

However, due to glitches in the logic that writes into MYCOLUMN,
sometimes it gets populated with values that are not castable to INT
(for example, a string with a carriage return in it). In that case, I
get -420 in my stored proc, and the query aborts.

What is the best way of rewriting a query like this to make sure that
it does not abort during execution? I am okay with INT(MYCOLUMN)
returning NULL on a dirty value.

I see several options, in order of decreasing preference:

1. Find a native DB2 casting function that would be tolerant of bad
charaters (could not find one so far).

2. Write a UDF that'll essentially only take digits from an input
string, and then convert that to INT.

I am going to fix the logic that writes into MYCOLUMN, to do my best to
have only values castable to INT. However, I'd like to see if may be
there's a function for 1) above, or if someone sees a better solution.

Thanks
Bogdan Sheptunov

Hello,

I have a column of type VARCHAR, storing some generally character data.
A few of the values in that column are in fact numbers represented as
strings (e.g., "12345").

At some moment I need to select those numbers, cast them to INT and do
some math on them. Naturally, I do something like this:

SELECT INT(MYCOLUMN) WHERE I_BELIEVE_THIS_IS_A_NUMBER='true'

However, due to glitches in the logic that writes into MYCOLUMN,
sometimes it gets populated with values that are not castable to INT
(for example, a string with a carriage return in it). In that case, I
get -420 in my stored proc, and the query aborts.

What is the best way of rewriting a query like this to make sure that
it does not abort during execution? I am okay with INT(MYCOLUMN)
returning NULL on a dirty value.

I see several options, in order of decreasing preference:

1. Find a native DB2 casting function that would be tolerant of bad
charaters (could not find one so far).

2. Write a UDF that'll essentially only take digits from an input
string, and then convert that to INT.

I am going to fix the logic that writes into MYCOLUMN, to do my best to
have only values castable to INT. However, I'd like to see if may be
there's a function for 1) above, or if someone sees a better solution.

Thanks
Bogdan Sheptunov