Safe cast from string to integer

[kangaroo]

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

[Serge Rielau]

Of the top I can only think of one way (short of parsing yourself):

db2 -td@

CREATE PROCEDURE friendlycast(IN txt VARCHAR(20), OUT num INTEGER)
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE 22018
BEGIN
SET num = NULL;
END;
num = INTEGER(txt);
END
@

CREATE FUNCTION friendlycast(txt VARCHAR)
RETURNS INTEGER
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE num INTEGER;
CALL friendlycast(txt, num);
RETURN num;
END
@

That should do it (untested). Don't expect this to break any speed
records. Consider it punishement for lack of data cleansing ;-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[aj]

I have the same sort of issue, and use this:

CREATE FUNCTION GETNUMBER(p_input VARCHAR(50))
RETURNS Integer
LANGUAGE SQL

CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION

RETURN
CASE WHEN
REPLACE(TRANSLATE(p_input, '',
'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz_. !'), ' ', '') =
'' then 0
ELSE CAST(REPLACE(TRANSLATE(p_input, '',
'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz_. !'),
' ', '') AS INTEGER)

If it can cast p_input as an INT, it returns it, otherwise it
returns a zero..

HTH

aj

[kangaroo]

Serge, aj,

thank you.

Bogdan

[Tonkuma]

kangaroo,

GETNUMBER returns integer even if input parameter(p_input) is mixed
digits and alphabet. Does it meet your requirement?

For example:
------------------------- Commands Entered -------------------------
VALUES GETNUMBER('1A2B3C4D5E');
--------------------------------------------------------------------

1
-----------
12345

1 record(s) selected.

[kangaroo]

Tonkuma,

no, it does not.

Here's a solution proposed by my coworker, who essentially joined
Serge's and aj's solutions:

CREATE FUNCTION GET_INT (vc_in VARCHAR (500))
RETURNS INTEGER

LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN CASE

WHEN vc_in = '' THEN NULL
WHEN TRANSLATE (vc_in, '', '1234567890') = '' THEN CAST (vc_in AS
INTEGER)
ELSE NULL
END
@

Bogdan

[Brian Tkatch]

The only issue with that, which you may not worry about, is preceding
and trailing spaces. Trailing spaces would be an issue if the field is
CHAR. The INTEGER() FUNCTION itself allows preceding spaces.

So:

CREATE FUNCTION Get_Int(Text VARCHAR(4000))
RETURNS INTEGER
SPECIFIC Get_Int
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
CASE
WHEN LTRIM(RTRIM(TRANSLATE(Text, '1', '1234567890'))) =
REPEAT('1', LENGTH(LTRIM(RTRIM(Text))))
THEN INTEGER(Text)
ELSE NULL
END

If you really don't want preceding or trailing spaces:

CREATE FUNCTION Get_Int(Text VARCHAR(4000))
RETURNS INTEGER
SPECIFIC Get_Int
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
CASE
WHEN TRANSLATE(Text, '1', '1234567890') = REPEAT('1', LENGTH(Text))
THEN INTEGER(Text)
ELSE NULL
END

B.

[Brian Tkatch]

Oops, forgot to repeat those 1s. Hmm.. and once at it, might as well
remove the 1 from the TRANSLATE.

CREATE FUNCTION Get_Int(Text VARCHAR(4000))
RETURNS INTEGER
SPECIFIC Get_Int
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
CASE

WHEN LTRIM(RTRIM(TRANSLATE(Text, '111111111', '234567890'))) =

REPEAT('1', LENGTH(LTRIM(RTRIM(Text))))
THEN INTEGER(Text)
ELSE NULL
END

If you really don't want preceding or trailing spaces:

CREATE FUNCTION Get_Int(Text VARCHAR(4000))
RETURNS INTEGER
SPECIFIC Get_Int
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
CASE

WHEN TRANSLATE(Text, '111111111', '234567890') = REPEAT('1',