Changing the default lowercase e for SQL fetched exponents

[SciJames]

Hi all,

I have a datapage where I'm pulling some very large and/or very small values from SQL and displaying them on the page. When I tried pulling them as number values it would give me the values as displayed in the table (eg 0.000000001). I wanted them in exponent form, and found that when I cast the returned value as varchar it would come back in the desired format (eg 1e-09). This is for a science based application, so I don't want the e being construed as euler's number. I was wondering if there was any way to change the way the exponent is displayed, capital E or 10^-09 or something similar.

There's some logic at the front of my code such that if we have no records, return nothing; and if there are records but no chemical values, return NA. This is the code I'm using:

CASE
WHEN (SELECT Count(ReferenceID) FROM tblChemicalValues WHERE ChemicalID=target.[@field:tblChemicalValues_1_1_1_ChemicalID] AND ChemValueTypeID=12 AND PublicationID=[@field:tblChemicalValues_1_1_1_PublicationID])=0
THEN ''
WHEN (SELECT Count(ChemValue) FROM tblChemicalValues WHERE ChemicalID=target.[@field:tblChemicalValues_1_1_1_ChemicalID] AND ChemValueTypeID=12 AND PublicationID=[@field:tblChemicalValues_1_1_1_PublicationID])=0
THEN 'NA'
ELSE Cast((SELECT Max(ChemValue) FROM tblChemicalValues WHERE ChemicalID=target.[@field:tblChemicalValues_1_1_1_ChemicalID] AND ChemValueTypeID=12 AND PublicationID=[@field:tblChemicalValues_1_1_1_PublicationID]) AS VARCHAR)
END

The frontend looks like this:

1.7e-006

Thanks!

[DrSimi]

Hi SciJames,

I believe the conversion you are seeing is done by SQL Server, not directly by Caspio. You can try to change the formatting of the output when converting from decimal to varchar by processing the resulting string separately, using substring, charindex or possibly also power functions.

Maybe this would help https://stackoverflow.com/a/70383697

[Jodie]

Hi SciJames - I tried to replicate - but when I use varchar I get the full value as a text string. Have you added custom formatting on the datapage? 

If the e is saving as a text field, then you should be able to replace the lower case e with either text options you'd prefer using string function REPLACE([@field],'e','E'); But if you haven't already tried, can you achieve this in localisation settings, in the formatting and number option?