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 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
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
Serge, aj,
thank you.
Bogdan
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.
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
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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 ?...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |