Re: Using SQL to convert an alpha field to numeric

Get this concept: Modular code.
Someone, somewhere, has to have a function to already do this.
Other things to think of:
What happens if the data has invalid data, like alphanumeric characters?
For "bad data" what do you want returned? I'm a big fan of nulls. Some
may prefer zeros. Some may prefer a special value which sticks out, like
all nines. In my case I would return nulls and then you could do your own
IFNULL to convert that to your desired value.
select a.F8, ifnull(ConvertToNumber(a.F8), 0) from a;

I know NOTHING about this stuff, but I threw one together as a learning
experience.

create function rob.ConvertToNumber
(InputCharacter varchar(20))
returns decimal(15, 2)
language sql
deterministic
returns null on null input
Begin
Declare StrippedClean varchar (20);
Declare ReturnDecimal decimal(15, 2);
Declare InvalidDecimal CONDITION FOR '22018';
Declare EXIT HANDLER FOR InvalidDecimal
return null;
set StrippedClean = replace(trim(InputCharacter), ',', '');
set ReturnDecimal = decimal(StrippedClean, 15, 2);
return ReturnDecimal;
end;

Now, here is some sampling:
values replace('123,456.78', ',', ''); strip out comma and replace with
null
values decimal('123.45', 15, 2);
values decimal('12x.45', 15, 2); should throw an error
values rob.ConvertToNumber('123.45');
values rob.ConvertToNumber('12x.45'); returns a null instead of throwing
an error
values rob.ConvertToNumber('123,456.78'); returns 123456.78 as a decimal.

So now you should be able to do
select a.F8, rob.ConvertToNumber(a.F8) from a;

http://www.redbooks.ibm.com/redbooks/pdfs/sg248326.pdf

Rob Berendt