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



  • 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:

    enter image description here

    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?



  • 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

    enter image description here

    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...

    enter image description here




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2