473,324 Members | 2,248 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

Dec 5 '05 #1
7 52354
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
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
Serge, aj,

thank you.

Bogdan

Dec 5 '05 #4
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

31
by: Jamie Burns | last post by:
Hello, I am writing a client / server application. There is 1 server, and many clients. The server processes requests from each client, and typically creates and manipulates C++ objects on their...
2
by: grahamo | last post by:
Hi, i know I should use the new style casts and I intend to however I would like to know how I go about this; I have an unsigned char* that a 3rd party API returned to me. The API reads text...
3
by: John Howard | last post by:
Making the following call to a local MSAccess database works fine: Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs) Dim intRows As Integer Dim strSQL As String Dim ds As New...
1
by: Hifni Shahzard | last post by:
Hi, I got a stored procedure, where it returns a value. But if I execute it. It gives an error as "Invalid cast from System.Int32 to System.Byte.". To make clear how do I execute this, below I'm...
3
by: Mike Cooper | last post by:
Hello All! I am getting teh above error message on the following code: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim dgt As...
3
by: BoloBaby | last post by:
All, I believe I am having a threading problem. Class "BELights" is part of a larger DLL that is used by my main application. A user control (of type BESeat) within the main application raises...
6
by: John-Arne Lillebø | last post by:
Hi. I run into this problem and i could need some help to solve it. The project is an ASP.NET Web project. Including code sample of the problem. Any idea what is causing the error message ?...
11
by: Dinsdale | last post by:
I am trying to determine what the current cast of an object is. Essentially, if I use GetType, it always returns the original type of the object created but I want to know what the current cast of...
18
by: digz | last post by:
Hi , I am trying to write a class that encapsulates a Union within it , I intend the code to be exception safe. can someone please review it and tell me if I have done the right thing , are...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.