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
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
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
thank you.
Bogdan
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.
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
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.
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',