By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,190 Members | 789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,190 IT Pros & Developers. It's quick & easy.

Safe cast from string to integer

P: n/a
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

Dec 5 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
kangaroo wrote:
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

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
Dec 5 '05 #2

P: n/a
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, '',
'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxY yZz_. !'), ' ', '') =
'' then 0
ELSE CAST(REPLACE(TRANSLATE(p_input, '',
'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxY yZz_. !'),
' ', '') AS INTEGER)

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

HTH

aj

kangaroo wrote:
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

Dec 5 '05 #3

P: n/a
Serge, aj,

thank you.

Bogdan

Dec 5 '05 #4

P: n/a
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.

Dec 6 '05 #5

P: n/a
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

Dec 8 '05 #6

P: n/a
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.

Dec 12 '05 #7

P: n/a
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',
LENGTH(Text))
THEN INTEGER(Text)
ELSE NULL
END

B.

Dec 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.