Re: Using SQL to convert an alpha field to numeric

from: Alan Shore <ashore@xxxxxxxx>
subject: Using SQL to convert an alpha field to numeric

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