Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Newbie question..

0 views
Skip to first unread message

Joe

unread,
Jun 7, 2006, 12:39:08 PM6/7/06
to
Hi everyone,
 
I was wondering if anyone can tell me how to convert a character string to numeric in DB2 version 8.2. I have tried the following, but, it won't work:
 
select INTEGER('1234567899') from a.table1
 
Also tried decimal function, but, no luck.
 
The column in the table is ten characters long.
 
Any help is very much appreciated.
 
-J.

Serge Rielau

unread,
Jun 7, 2006, 12:46:39 PM6/7/06
to
Joe wrote:
> Hi everyone,
>
> I was wondering if anyone can tell me how to convert a character string
> to numeric in DB2 version 8.2. I have tried the following, but, it won't
> work:
>
> select INTEGER('1234567899') from a.table1
What didn't work, and how?
Here is what I get:
db2 => select INTEGER('1234567899') from sysibm.sysdummy1;

1
-----------
1234567899

1 record(s) selected.


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/

Joe

unread,
Jun 7, 2006, 1:55:29 PM6/7/06
to
Database field type is varchar(10). Exmple:
select INTEGER(field1) from a.table1

I get the following error messasge:
Error: SQL0420N Invalid character found in a character string argument of
the function "INTEGER". SQLSTATE=22018

(State:22018, Native Code: FFFFFE5C)

0 Row(s) affected

SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018

-J.

"Serge Rielau" <sri...@ca.ibm.com> wrote in message
news:4eoe6pF...@individual.net...

Mark A

unread,
Jun 7, 2006, 2:27:45 PM6/7/06
to
"Joe" <sirg...@hotmail.com> wrote in message
news:e673ug$2cr$1...@news1.ucsd.edu...

> Database field type is varchar(10). Exmple:
> select INTEGER(field1) from a.table1
>
> I get the following error messasge:
> Error: SQL0420N Invalid character found in a character string argument of
> the function "INTEGER". SQLSTATE=22018
>
> (State:22018, Native Code: FFFFFE5C)
>
> 0 Row(s) affected
>
> SQL0420N Invalid character found in a character string argument of the
> function "INTEGER". SQLSTATE=22018
>
> -J.
>
From the DB2 sample database:

select cast (empno as integer) from emp


Joe

unread,
Jun 7, 2006, 2:47:29 PM6/7/06
to
I get the same error message when using cast..

-J.

"Mark A" <nob...@nowhere.com> wrote in message
news:9ZydnQYWGYsChxrZ...@comcast.com...

Serge Rielau

unread,
Jun 7, 2006, 4:52:26 PM6/7/06
to
Joe wrote:
> I get the same error message when using cast..
Joe, one of your rows has a string in the column that is not a number.
How many rows do you have? You can either eyeball the problem or write a
query that finds non numerics. (E.g. using TRANSLATE to map 0-9 to empty
string and looking and see which one doesn't end up empty...

Cheers
Serge

Joe

unread,
Jun 7, 2006, 5:01:40 PM6/7/06
to
Thanks much, Serge. I will give this a try - I think you are right...

-J.
"Serge Rielau" <sri...@ca.ibm.com> wrote in message

news:4eosjnF...@individual.net...

0 new messages