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.

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/ond...ness/conf2006/

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" <srielau@ca.ibm.com> wrote in message
news:4eoe6pF1f26aoU1@individual.net...
> 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/ond...ness/conf2006/

"Joe" <sirguicho@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

I get the same error message when using cast..

-J.

"Mark A" <nobody@nowhere.com> wrote in message
news:9ZydnQYWGYsChxrZnZ2dnUVZ_t2dnZ2d@comcast.com. ..
> "Joe" <sirguicho@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 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


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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

Thanks much, Serge. I will give this a try - I think you are right...

-J.
"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:4eosjnF1fhiksU1@individual.net...
> 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
>
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
>
> IOD Conference
> http://www.ibm.com/software/data/ond...ness/conf2006/