Re: Using SQL to convert an alpha field to numeric

Good advice Rob. Using functions is how I handle these myself.

You may want at least two functions:
1) The first returns only the numeric bytes found, including the decimal
point, and discards the rest.
2) The second returns NULL (only) if any alpha or other bad data was found.
Commas and decimal points not considered as bad data. If no alpha or bad
data found, it returns numeric bytes plus decimal point byte.

These type of conversions are a common use case of mine, so I use functions
so I don't have to re-think or re-code it each time.

Using NULLs wisely in SQL is very helpful. In RPG they are more of a PITA
when NULL indicators get involved, imo.

Mike

date: Thu, 8 Mar 2018 12:45:32 -0500
from: Rob Berendt <rob@xxxxxxxxx>
subject: 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
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com