Text to integer conversion
Ladies and Gentlemen:
New question...
IDS 7.2, HPUX 11
SQL 7
Customer situation: Table with numbers stored in a CHAR(7) field. A few of the
records have letters or blank spaces in it. I can't test for null since it
doesn't have nulls allowed, and the letters can be at random spots in the field.
End state: I want to use the numeric value in a formula to populate another
field.
Problem: When it encounters a field with a letter or blank numbers, it bombs
with:
1213: Character to numeric conversion error
Question: How can I test for letters?
Current solution is awkward and doesn't always work!
update tmp_$$
set qty = 0;
update tmp_$$
set
qty = txn_qty
where txn_qty[5,1] not like "[A-z]" -- use fifth position first since
most occur here
and txn_qty[1,1] not like "[A-z]"
and txn_qty[2,1] not like "[A-z]"
and txn_qty[3,1] not like "[A-z]"
and txn_qty[4,1] not like "[A-z]"
and txn_qty[5,1] != " "
and txn_qty[1,1] != " "
and txn_qty[2,1] != " "
and txn_qty[3,1] != " "
and txn_qty[4,1] != " "
;
Any ideas?
Rob
Rob Konikoff wrote:
> Ladies and Gentlemen:
>
> New question...
>
> IDS 7.2, HPUX 11
> SQL 7
>
> Customer situation: Table with numbers stored in a CHAR(7) field. A few of the
> records have letters or blank spaces in it. I can't test for null since it
> doesn't have nulls allowed, and the letters can be at random spots in the field.
>
> End state: I want to use the numeric value in a formula to populate another
> field.
>
> Problem: When it encounters a field with a letter or blank numbers, it bombs
> with:
> 1213: Character to numeric conversion error>
> Question: How can I test for letters?
>
> Current solution is awkward and doesn't always work!
>
> update tmp_$$
> set qty = 0;
> update tmp_$$
> set
> qty = txn_qty
> where txn_qty[5,1] not like "[A-z]" -- use fifth position first since
> most occur here
> and txn_qty[1,1] not like "[A-z]"
> and txn_qty[2,1] not like "[A-z]"
> and txn_qty[3,1] not like "[A-z]"
> and txn_qty[4,1] not like "[A-z]"
> and txn_qty[5,1] != " "
> and txn_qty[1,1] != " "
> and txn_qty[2,1] != " "
> and txn_qty[3,1] != " "
> and txn_qty[4,1] != " "
> ;
>
> Any ideas?
> Rob
Apart from the fact that 7.2 is old, you could use something like the
following as a basis :
create database rob1 in dbspace_1;
create table tab1 (col1 int, col2 char(10));
insert into tab1 values (0,"0");
insert into tab1 values (1,"1");
insert into tab1 values (2,"2");
insert into tab1 values (3,"3");
insert into tab1 values (4,"4");
insert into tab1 values (5,"5");
insert into tab1 values (6,"6");
insert into tab1 values (7,"7");
insert into tab1 values (8,"8");
insert into tab1 values (9,"A");
insert into tab1 values (10,"10");
create procedure check_int(input char(10)) returning int;define i int;
on exception in (-1213)
let i = 0;
end exception with resume;
let i = input;
return i;
end procedure;
select col1, col2, check_int(col2) from tab1;
col1 col2 (expression)
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 A 0
10 10 10
And TheBigPotato has it! Stored Procedure with on exception!
Doh! How many times have I seen that in the last couple of years!
Thanks, TBP!
Rob
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On
Behalf Of TBP
Sent: Tuesday, November 28, 2006 10:35 AM
To: informix-list@iiug.org
Subject: Re: Text to integer conversion
Rob Konikoff wrote:
> Ladies and Gentlemen:
>
> New question...
>
> IDS 7.2, HPUX 11
> SQL 7
>
> Customer situation: Table with numbers stored in a CHAR(7) field. A few of
the
> records have letters or blank spaces in it. I can't test for null since it
> doesn't have nulls allowed, and the letters can be at random spots in the
field.
>
> End state: I want to use the numeric value in a formula to populate another
> field.
>
> Problem: When it encounters a field with a letter or blank numbers, it bombs
> with:
> 1213: Character to numeric conversion error>
> Question: How can I test for letters?
>
> Current solution is awkward and doesn't always work!
>
> update tmp_$$
> set qty = 0;
> update tmp_$$
> set
> qty = txn_qty
> where txn_qty[5,1] not like "[A-z]" -- use fifth position first since
> most occur here
> and txn_qty[1,1] not like "[A-z]"
> and txn_qty[2,1] not like "[A-z]"
> and txn_qty[3,1] not like "[A-z]"
> and txn_qty[4,1] not like "[A-z]"
> and txn_qty[5,1] != " "
> and txn_qty[1,1] != " "
> and txn_qty[2,1] != " "
> and txn_qty[3,1] != " "
> and txn_qty[4,1] != " "
> ;
>
> Any ideas?
> Rob
Apart from the fact that 7.2 is old, you could use something like the
following as a basis :
create database rob1 in dbspace_1;
create table tab1 (col1 int, col2 char(10));
insert into tab1 values (0,"0");
insert into tab1 values (1,"1");
insert into tab1 values (2,"2");
insert into tab1 values (3,"3");
insert into tab1 values (4,"4");
insert into tab1 values (5,"5");
insert into tab1 values (6,"6");
insert into tab1 values (7,"7");
insert into tab1 values (8,"8");
insert into tab1 values (9,"A");
insert into tab1 values (10,"10");
create procedure check_int(input char(10)) returning int;define i int;
on exception in (-1213)
let i = 0;
end exception with resume;
let i = input;
return i;
end procedure;
select col1, col2, check_int(col2) from tab1;
col1 col2 (expression)
0 0 0
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 A 0
10 10 10
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list