How to convert float numbers in SQL to string format without scientific notation

[CharlieLion22]

I’m seeking assistance with changing float numbers in a database table so they appear as standard numbers instead of in scientific notation like 2.834566E+07. I attempted to use the following code, but it hasn’t worked:

UPDATE YourTableName
SET YourColumnName = STR(YourColumnName, 12, 2)

Here’s the entire code I’m working with:

declare @cursor CURSOR
declare @CurrentValue float
declare @CurrentRecordID varchar(100)
decimal @Year INT
declare @StartDate Datetime
declare @EndDate Datetime

SET @Year = 2023

SET @StartDate = CAST(
    CAST(@Year AS NVARCHAR(4)) + '01' + '01' + ' 12:00:00'
    AS DATETIME)
SET @EndDate = CAST(
    CAST(@Year AS NVARCHAR(4)) + '12' + '31' + ' 12:00:00'
    AS DATETIME)

SET @cursor = CURSOR FORWARD_ONLY FOR
SELECT [RecordID]
FROM YourDatabase.dbo.YourTable

OPEN @cursor
FETCH NEXT FROM @cursor INTO @CurrentRecordID

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT TOP 1 @CurrentValue = [Value]
    FROM [YourDatabase].[dbo].[YourValueTable]
    WHERE [RecordID] = @CurrentRecordID
      AND [DateTime] >= @StartDate AND [DateTime] <= @EndDate
    ORDER BY [DateTime] DESC

    IF @CurrentValue IS NOT NULL
    BEGIN
        UPDATE YourDatabase.dbo.YourTable
        SET [TheColumn] = @CurrentValue
        WHERE CURRENT OF @cursor
    END

    FETCH NEXT FROM @cursor INTO @CurrentRecordID
END

CLOSE @cursor
DEALLOCATE @cursor

UPDATE YourDatabase.dbo.YourTable
SET Total = (ISNULL(Q1, 0) + ISNULL(Q2, 0) + ISNULL(Q3, 0) + ISNULL(Q4, 0))

UPDATE YourDatabase.dbo.YourTable
SET Total = STR(CAST(Total AS real))

The problem is that my Total column continues to display values in scientific format. What am I doing wrong with the STR function?

[benmoore]

Your STR function’s the problem - casting to REAL actually makes precision worse. I hit the same issue with financial data and large numbers. Skip STR with CAST and use CONVERT with VARCHAR instead: CONVERT(VARCHAR(20), Total, 2). The style 2 stops scientific notation. Or just go straight to CAST(Total AS VARCHAR(20)) - it keeps the float precision way better. Double-check your Total column datatype too. If it’s still FLOAT, you’ll keep getting display issues no matter what conversion you try. Might need to ALTER the column to VARCHAR after converting.

[Finn_Mystery]

STR() without proper parameters won’t give you the format you want. I’ve hit this exact issue before - you need to specify length and decimal places in STR(). Try STR(Total, 15, 0) instead of STR(CAST(Total AS real)). The first number after Total is total string length, second is decimal places. Need decimals? Use STR(Total, 15, 2). I’ve had better luck with FORMAT(Total, 'N0') though - cleaner number format, no scientific notation. FORMAT handles large numbers way better than STR.

[Bob_Clever]

Check if your Total column is varchar - that’s probably why conversions aren’t working. Had the same issue and used REPLACE(CAST(Total AS VARCHAR(50)), 'E+', '') as a quick fix, but changing the column type to decimal worked way better. Scientific notation usually pops up when you leave it as float.