RE: SQL String to numeric conversion

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