Re: Using SQL to convert an alpha field to numeric

[Charles Wilt <charles.wilt@xxxxxxxxx>]

I'd use replace...

select decimal(replace('1,234.56',',',''),7,2)
from sysibm.sysdummy1



On Thu, Mar 8, 2018 at 9:33 AM, Alan Shore <ashore@xxxxxxxx> wrote:

> 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