SQL is rounding my decimal on cast

Question

I'm using SQL Server 2005. The datatype is varchar. I'm trying to convert numbers like

1250000

to

1.25

within the SQL query and drop the trailing zeroes. I have tried a number of things with no success - have run into 'trim is not a function', etc.

Here's what I currently have, in each iteration that I have attempted.

select top 30 
    var1, var2, var3, var4, 
    CONVERT(DECIMAL(6,2), (var5 / 1000000)) as 'Number' 
from 
    databasetable 
where 
    var1 = x 

select top 30 
    var1, var2, var3, var4, 
    cast((var5 / 1000000) as decimal(6,2)) as 'Number' 
from 
    databasetable 
where 
    var1 = x 

Both queries above are rounding to the nearest million, i.e. 1250000 becomes 1.00, etc. Any ideas would be much appreciated. Changing decimal to numeric did not help either.

Answer 1

DECLARE @MyTable TABLE
(
    ColA VARCHAR(50) NOT NULL
);
INSERT INTO @MyTable (ColA) VALUES ('1250000');
INSERT INTO @MyTable (ColA) VALUES ('125');
INSERT INTO @MyTable (ColA) VALUES ('12');
INSERT INTO @MyTable (ColA) VALUES ('1');
INSERT INTO @MyTable (ColA) VALUES ('');
INSERT INTO @MyTable (ColA) VALUES ('e5');
INSERT INTO @MyTable (ColA) VALUES ('0x34abc');

SELECT  *, 
        w.ColB / 1000000.0 AS ColC,
        CONVERT(NUMERIC(4,2), w.ColB / 1000000.0) AS ColD, -- The result is rounded to 2 decimals; You should change default precision (4) and scale (2)
        STR(w.ColB / 1000000.0, 5, 2) AS ColE, -- The result is rounded to 2 decimals; You should change default lenght (5) and scale (2)       
        w.ColB * 1.0 / POWER(10, ColLength-1) AS ColF
FROM
(
    SELECT  *,
            CASE 
                -- It assumes that all values are INT[egers] without sign (+/-)
                WHEN PATINDEX('%[^0-9]%', RTRIM(LTRIM(v.ColA))) = 0 AND LEN(LTRIM(v.ColA)) BETWEEN 1 AND 11 THEN CONVERT(INT, v.ColA)
                ELSE NULL -- It returns NULL if the value cann't be converted to INT
            END AS ColB,
            LEN(LTRIM(v.ColA)) AS ColLength
    FROM    @MyTable v
) w

Results:

ColA    ColB    ColLength ColC        ColD ColE  ColF
------- ------- --------- ----------- ---- ----- --------------
1250000 1250000 7         1.250000000 1.25  1.25 1.250000000000
125     125     3         0.000125000 0.00  0.00 1.250000000000
12      12      2         0.000012000 0.00  0.00 1.200000000000
1       1       1         0.000001000 0.00  0.00 1.000000000000
        NULL    0         NULL        NULL NULL  NULL
e5      NULL    2         NULL        NULL NULL  NULL
0x34abc NULL    7         NULL        NULL NULL  NULL

Answer 2

I can't say for sure without know you're exact platform, but my guess would be its truncating it to an int. I would try converting var5 and 10000000 before doing the math. Something like

// NOTE, I have not tried this code
CONVERT(DECIMAL(6,2), (CONVERT(DECIMAL(6,2), var5) / CONVERT(DECIMAL(6,2), 1000000)))

Answer 3

   select convert(decimal(5,2),convert(decimal(10,3),var5) / 1000000) where isnumeric(var5)=1

Answer 4

@marc_s is absolutely right - stop storing numbers as strings!

That said, you're a victim of integer math. Try:

SELECT CONVERT(DECIMAL(10,2), (var5 / 1000000.0)) 

Since you stored numbers as strings, SQL Server may try to perform this calculation with non-numeric data before applying the filter, so you can say:

SELECT CONVERT(DECIMAL(10,2), (CASE WHEN ISNUMERIC(var5) = 1 THEN var5 END / 1000000.0))

[Note that this isn't perfect either.]

If this doesn't work then you've got some bad data. If the following does work but yields too many decimal places:

select 
    var1, ...,
    CONVERT(DECIMAL(10,2), var5) / 1000000 as [Number] 

Then try wrapping it with an extra convert:

select 
    var1, ...,
    CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,2), var5) / 1000000) as [Number]

That all said, why can't you format this number in the presentation layer?

Also, please don't use 'single quotes' for column aliases ... this syntax is deprecated in some forms, and it incorrectly makes your column alias look like a string (IMHO). Use [square brackets] or just avoid using keywords as aliases.