RE: SQL String to numeric conversion

Hi David
I also have an interest in this little problem, and you have a good answer.
The only problem I have is if the value of the field is blank (no $ sign)

This results in an answer of +++++++++


Alan Shore
Programmer/Analyst, Distribution
E:AShore@xxxxxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill


midrange-l-bounces@xxxxxxxxxxxx wrote on 01/22/2009 02:36:53 AM:

From the RPG list

I had a go at this :

My sample data : ( you didn't give exact information )
select $ from dollars
....+....1
$
$ 302.54
$1.59-
$33.60-


My statement :
with mytable as (
SELECT length(trim($)), locate('.', trim($)), substr(trim($), 2,
locate('.', trim($))-2) as dollars , substr(trim($), locate('.',
trim($))+1, 2) as cents , right(trim($), 1) as sign FROM dollars
)

select case when sign ='-' then dollars concat cents
*-1 else dollars concat cents end as cents
from mytable

Result is a list in cents :
...1....
CENTS
30.254
159-
3.360-


I didn't want to go further because we need more details about your data.





-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-
bounces@xxxxxxxxxxxx] De la part de Nick W Mart
Envoyé : mercredi 21 janvier 2009 16:21
À : RPG programming on the IBM i / System i
Objet : Re: SQL String to numeric conversion

I have more information regarding my previous question. Here is a
sample of the actual data field.

....+....1....+....2....+....3....+....4....+....5....+....6....
+....7....+....8....+....9....+... INFO TO PRINT $1.59-
$.00 $1.59- $.00$.00 $.00
$.00 $.00 $.00$.00 $.00
$.00 $.00 $.00$.00 $33.60-
$.00 $33.60- $.00$.00 $.00
$.00 $.00 $.00$.00 $.00
$.00 $.00 $.00$.00 $.00
$.00 $.00 $.00$.00 $.00
$.00 $.00 $.00$.00 $.00
$.00 $.00 $.00$.00 $.00
$.00 $.00 $.00$.00 $.00
$.00 $.00 $.00$.00 !
$.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00 $.00 $.00 $.00
$.00$.00We came up with the following SQL statement that gave us 2
totals.select sum(decimal(replace((substring(fldname,1,17)),'$',''),
9,2,'.')) FROMfilename WHERE recid = '3' GROUP BY substring(fldname,
18,1)If there is something that would be better, we would appreciate
thesuggestions.Nick Mart----- Original Message -----From: "Nick W
Mart" <nickmart@xxxxxxxxxxxxxxx>To: "RPG programming on the AS400 /
iSeries" <rpg400-l@xxxxxxxxxxxx>Sent: Wednesday, January 21, 2009 8:
07 AMSubject: SQL String to numeric conversion> If I have a value of
"$38.54-" in a text field is there a way that youknow of in sql so
that I can do a sum on all records?>> Thank you in advance.>> Nick Mar!
t> --> This is the RPG programming on the IBM i / System i (RPG400-L)
mailinglist> To post a message email: RPG400-L@xxxxxxxxxxxx> To
mailinglist> subscribe, unsubscribe, or change list options,> visit:
mailinglist> http://lists.midrange.com/mailman/listinfo/rpg400-l> or
mailinglist> email: RPG400-L-request@xxxxxxxxxxxx> Before posting,
mailinglist> please take a moment to review the archives> at
mailinglist> http://archive.midrange.com/rpg400-l.>---------------------
mailinglist> -----------------------------------------------------------
mailinglist> No virus found in this incoming message.Checked by AVG -
mailinglist> http://www.avg.comVersion: 8.0.176 / Virus Database:
mailinglist> 270.10.10/1905 - Release Date: 1/20/20092:34 PM
--
This is the RPG programming on the IBM i / System i (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http:
//archive.midrange.com/rpg400-l.

--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.