RE: SQL String to numeric conversion

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

This looks like an ugly problem to deal with in SQL, but I think it's
possible.  Rather than trying to come up with a different solution, I just
altered yours slightly. See if it works for all cases.

with prob01a as                                               
(select replace(field04, '$', '') as money from prob01) select
money, case money when ' ' then 0 else                        
case when position('-' IN money) > 0 then                     
decimal(replace(money,'-',''),9,2) * -1                       
else                                                          
decimal(money,9,2,'.') end end                                
FROM prob01a                                                  

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: Re: SQL String to numeric conversion

Afternoon all
I took some of the responses answering the original request because I have
somewhat of the same problem.
I have a file (PROB01) containing the following data in a particular field
(Field04)
Field04

$
$302.54
3.66
1.54-
33.60-
$ 11.21-
2.22
.34
$ .36
$11

Using the following sql statement
with prob01a as
(select replace(field04, '$', '') as money from prob01)
select  money, case money when ' ' then 0 else
decimal(money,9,2,'.') end
FROM prob01a
I get the following results
MONEY             CASE expression
                        .00
                        .00
302.54                  302.54
3.66                    3.66
1.54-             +++++++++++++
33.60-            +++++++++++++
 11.21-           +++++++++++++
2.22                    2.22
.34                     .34
 .36                    .36
11                      11.00

as you can see the problem I am having is with the minus sign, and for the
life of me I just cannot seem to come up with the correct solution to
satisfy all conditions
Anyone any ideas?



Alan Shore