SQL Server Performance

remove trailing zeros after decimal point in money data type column

Discussion in 'SQL Server 2005 General Developer Questions' started by sultani.khalid.af, May 23, 2009.

  1. sultani.khalid.af New Member

    i need select statement to remove trailing zeros from money column after decimal point else keep the column data in same format say there is a column named cost and table name table1
    cost
    12.0000
    13.0001
    12.0022
    output
    12
    13.0001
    12.0022
  2. moh_hassan20 New Member

    example:
    use adventureworks
    go
    SELECT SubTotal, REPLACE(cast (subtotal as decimal(20,4)) ,'.0000','')
    from Sales.SalesOrderHeader
    order by SalesOrderID
  3. sultani.khalid.af New Member

    hi moh_assan i don't have adventure works database i tried in my own practice database , it still add two zeros to the end if it contains 0, but it removed 00 if there is not other number after decimal.
    i typed
    100.10
    300.222
    400.00
    500.50
    12.0001
    out put i got
    100.1000
    300.2220
    400
    500.5000
    12.0001
  4. moh_hassan20 New Member

    drop function dbo.TrimZero
    go

    CREATE FUNCTION dbo.TrimZero (@value decimal (38,8))
    RETURNS varchar(100)
    as
    begin
    DECLARE @n int,
    @n2s nvarchar(100),
    @ch char
    SET @n2s = cast (@value as varchar(100))

    SET @n = LEN(@n2s) +1

    WHILE @n > 0
    BEGIN
    SET @n = @n - 1
    set @ch = SUBSTRING(@n2s, @n, 1)
    IF @ch = '.'
    begin
    set @n = @n-1
    break
    end

    IF @ch = '0'
    CONTINUE
    else
    break

    END -- while

    return (LEFT(@n2s, @n))
    end
    --------------------
    go




    select 100.10, dbo.TrimZero (100.10)
    select 300.222, dbo.TrimZero (300.222)
    select 400.00 ,dbo.TrimZero (400.00)
    select 500.50 ,dbo.TrimZero (500.50)
    select 12.0001 , dbo.TrimZero (12.0001)

    results


    100.10 100.1

    300.222 300.222
    400.00 400
    500.50 500.5
    12.0001 12.0001



  5. sultani.khalid.af New Member

    Thanks Alot Mohammad Hasan problem is solved.
  6. FrankKalis Moderator

    Is there a reason why you don't want to do such stuff in your presentation layer? I think this is where it belongs.
  7. sultani.khalid.af New Member

    i know frank but what do you think regarding the performance will the performance decrease by doing this , and i think it require more work in presentation layer ex changing each column in gridview then compare to doing it in backend.
  8. FrankKalis Moderator

    If you put it into a UDF and call it in the SELECT list, you definitely are better off doing it at the client. Otherwise you would execute the UDF for each and every row, which is very likely to hurt performance.
    I'm not that familiar with .Net, but I would guess that it can't be that hard to do it there. May i say that I don't really understand that requirement anyway? 12 is value-wise the same as 12.000. It's just another representation.
  9. sultani.khalid.af New Member

    it was just a requirement,any way thanks for your suggestion i will handle it in presentation layer.

Share This Page