Re: Using SQL to convert an alpha field to numeric

[Charles Wilt <charles.wilt@xxxxxxxxx>]

If you're going to do that...

You should probably make sure to handle
negative numbers .... -, (), CR
use of comma instead of decimal point...

But yeah, I'm sure that's been built...I'd look for it before rolling my
own.



Charles


On Thu, Mar 8, 2018 at 10:45 AM, Rob Berendt <rob@xxxxxxxxx> wrote:

> 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
>
>
>
>
>
> From:   Alan Shore <ashore@xxxxxxxx>
> To:     "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
> Date:   03/08/2018 11:33 AM
> Subject:        Using SQL to convert an alpha field to numeric
> Sent by:        "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
>
>
>
> Hi everyone
> We are on V7r1
>
> I have received a file to be used in reconciling against our orders
> One of the fields (F8) is the order amount, but it is in an alpha field,
> left justified
> For example
> 2,239.72
> 60.61
> 53.41
> 626.92
>
> If I try and use the following
> decimal(a.F8, 7, 2)
> This works fine - except for the amount 2,239.72
> So I came up with the following
>
> case when length(trim(a.F8)) > 7 then
> decimal(substr(trim(a.F8), 1, (length(trim(a.F8)) - 7)) concat
> substr(trim(a.F8), (length(trim(a.F8)) - 5), 6), 7, 2) else
> decimal(a.F8, 7, 2)
> end
>
>
> This will work - unless I get amount into the millions - in which case I
> have to expand my case
>
> Now - here is my question
>
> Is there a "simpler" way to do this conversion
> I was somewhat surprised that
> decimal(a.F8, 7, 2)
> didn't handle the comma - or am I not seeing something?
>
>
> Alan Shore
> E-mail : ASHORE@xxxxxxxx
> Phone [O] : (631) 200-5019
> Phone [C] : (631) 880-8640
> 'If you're going through hell, keep going.'
> Winston Churchill
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD