How to retain same FLOAT value after converting it to a VARCHAR in Azure Synapse

Question

Consider the following example:

DROP TABLE dbo.TEST_FLOAT;
CREATE TABLE dbo.TEST_FLOAT (
    AMOUNT    FLOAT
);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (11546.25);
SELECT AMOUNT, CAST(AMOUNT AS VARCHAR(10)) AS AMOUNT_AS_CHAR 
FROM dbo.TEST_FLOAT

This is what is returned from Management Studio:

What I want is the displayed values to be the same, however it seems Azure Synapse is rounding the number when the CAST is performed.

I need this to work inline as I am hashing several concatenated values and need these all COALESCED VARCHARS.

Any idea how to do this?

Answer 1

Thanks to Ergest Basha who answered this in a comment:

Here is the result:

DROP TABLE dbo.TEST_FLOAT;
CREATE TABLE dbo.TEST_FLOAT (
    AMOUNT    FLOAT
);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (11546.25);
SELECT AMOUNT, 
       CAST(AMOUNT AS VARCHAR(10)) AS AMOUNT_AS_CHAR,
       CONVERT (VARCHAR(10), AMOUNT, 128) AS AMOUNT_AS_CHAR2
FROM dbo.TEST_FLOAT

This is now working in AMOUNT_AS_CHAR2

UPDATE

I just discovered this solution DOES NOT WORK when the amount is ZERO. My work around is as follows:

DROP TABLE dbo.TEST_FLOAT;
CREATE TABLE dbo.TEST_FLOAT (
    AMOUNT    FLOAT
);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (11546.25);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (0.0);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (-0.1);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (2.0);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (NULL);
SELECT AMOUNT, 
       CAST(AMOUNT AS VARCHAR(10)) AS AMOUNT_AS_CHAR,
       CONVERT(VARCHAR(10), AMOUNT, 128) AS AMOUNT_AS_CHAR2,
       CASE [AMOUNT] WHEN 0 THEN '0' ELSE CONVERT(VARCHAR(59), [AMOUNT], 128) END AS AMOUNT_AS_CHAR3
FROM dbo.TEST_FLOAT

Adding the INLINE CASE wrapper gets around the issue...