Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 9 of 9 total |
Casting BCD/Float to String with fixed decimal places |
Thu, Jun 24 2010 2:50 AM | Permanent Link |
Adam H. | Hi,
I was just wondering if it's possible to Casting a BCD or Float to String with fixed decimal places in SQL. In Pascal it would be something similar to result := floattostrf(MyTableTPrice.value, fffixed, 15, 2); Cheers Adam. |
Thu, Jun 24 2010 3:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
I'm sure someone out there will know a better way but I'd do one of two things 1: write a UDF 2: use CAST, POS & SUBSTR eg SUBSTR(CAST(float AS CHAR(15)),1,POS('.',CAST(float AS CHAR(15))))+SUBSTR(CAST(float AS CHAR(15)),POS('.',CAST(float AS CHAR(15))),4) Probably the wrong number of brackets and the decimal point may be missing - I always have to experiment with things like this Roy Lambert |
Thu, Jun 24 2010 12:16 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
I've been playing with it and this TRIM(BOTH ' 'FROM SUBSTR(CAST(_Fee AS VARCHAR(15)),1,POS('.',CAST(_Fee AS VARCHAR(15)))-1)) +TRIM(BOTH ' ' FROM SUBSTR(CAST(_Fee AS VARCHAR(15)),POS('.',CAST(_Fee AS VARCHAR(15))),4)) AS Formatted, works (its left justified) as long as there are decimals. If not it looks like an integer This seems to work IF( 0 = POS('.',CAST(_Fee AS VARCHAR(10))) THEN CAST(_Fee AS VARCHAR(7))+'.00' ELSE SUBSTR(CAST(_Fee AS VARCHAR(10)),1,POS('.',CAST(_Fee AS VARCHAR(10)))+2) ) you just hasve to adjust the 10 to whatever size field you want and the 2 to the number of decimals. Works for me in ElevateDB, not tested in DBISAM so let me know. Roy Lambert [Team Elevate] |
Thu, Jun 24 2010 5:56 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< I was just wondering if it's possible to Casting a BCD or Float to String with fixed decimal places in SQL. >> Try this (assuming that Test is a BCD field with a defined 4 decimal places): CAST(CAST(Test AS DECIMAL(19,2)) AS VARCHAR(20)) -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Jun 24 2010 7:30 PM | Permanent Link |
Adam H. | Hi Tim and Roy,
Thanks for your replies... Unfortunately neither of those seem to work in DBISam 4. Roy's approach works for some values, but not others oddly enough and Tim's doesn't seem to work at all. Either way - it looks like it's not straight forward, so I'll look for another approach. Thanks for your time and help guys! Adam |
Fri, Jun 25 2010 1:27 AM | Permanent Link |
Robert Kaplan | "Adam H." <ahairsub5@removeme.jvxp.com> wrote in message news:31BAB6A3-8D38-4011-B9DE-19A157B583FD@news.elevatesoft.com... > Hi Tim and Roy, > > Thanks for your replies... > > Unfortunately neither of those seem to work in DBISam 4. Roy's approach > works for some values, but not others oddly enough and Tim's doesn't seem > to work at all. > You need to catch the values for 1 decimal as in 12.1 select IF( 0 = POS('.',CAST(Famount AS VARCHAR(10))) THEN CAST(Famount AS VARCHAR(7))+'.00' ELSE IF (POS('.',CAST(Famount AS VARCHAR(10))) + 1 = LENGTH(CAST(FAMOUNT AS VARCHAR(10))) THEN CAST (FAMOUNT AS VARCHAR(9)) + '0' ELSE CAST(FAMOUNT AS VARCHAR(10)) )) from TABLE Robert |
Fri, Jun 25 2010 3:12 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
<giggle> I was going to say that Tim's got ElevateDB on the brain, but testing it doesn't even work in ElevateDB </giggle> I think Robert has spotted the problem, but if you are using a recent version of DBISAM I'd go for a UDF - more comprehensible. Roy Lambert [Team Elevate] |
Fri, Jun 25 2010 6:14 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< Unfortunately neither of those seem to work in DBISam 4. >> That's what I tested it with, but I think that the problem is that I tested it with numbers containing 4 decimal places - are you using numbers with *less* than 2 decimals ? If so, then just pad out the numeric string with zeros. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Jun 28 2010 5:02 AM | Permanent Link |
John Hay | Adam
> I was just wondering if it's possible to Casting a BCD or Float to > String with fixed decimal places in SQL. > > In Pascal it would be something similar to > > result := floattostrf(MyTableTPrice.value, fffixed, 15, 2); I add a value and then trim the result. To change the number of decimal places just change the value being added. eg 2 decimal places SELECT LEFT(CAST(MyField+0.001 AS CHAR(20)),LENGTH(CAST(MyField+0.001 as CHAR(20)))-1) FROM MyTable 4 decimal places SELECT LEFT(CAST(MyField+0.00001 AS CHAR(20)),LENGTH(CAST(MyField+0.00001 as CHAR(20)))-1) FROM MyTable John |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |