格式化编号 [英] Formating Currecny Number

问题描述

sql server中是否有任何内置函数可以格式化数字.

例如formatnumber(3.1,3)-> 3.100
formatnumber(2.314,2)-> 2.31
等等.

我已经完成了自己的功能，但是只是问我是否花时间写了

这是我的功能

Is there any built-in function in sql server to format a number.

e.g. formatnumber(3.1,3) --> 3.100
formatnumber(2.314,2) --> 2.31
and so on.

i have done my own function, but just asking if i waisted my time to write it

here is my function

CREATE FUNCTION [dbo].[UDF_FORMAT_NUMBER]
(
@NUMBER    FLOAT,
@DECIMAL_PLACES INT
)
RETURNS VARCHAR(500)
AS
BEGIN

    DECLARE @NUMBER_INT_PART BIGINT
    DECLARE @NUMBER_DECIMAL_PART FLOAT
    DECLARE @DECIMAL_NUMBER_STR VARCHAR(300)
    DECLARE @NUMBER_DECIMAL_PART_STR VARCHAR(300)

    -- GET INTEGER PART ONLY
    SET @NUMBER_INT_PART = CONVERT(BIGINT,@NUMBER)

    -- THIS WILL GIVE THE NUMBER AS FLOAT ALWAYS WILL BE 0.<DECIMAL_NUMBER>
    SET @NUMBER_DECIMAL_PART = @NUMBER - @NUMBER_INT_PART

    -- NOW WE WILL GET THE DECIMAL_PART ALONE
    SET @NUMBER_DECIMAL_PART_STR = RIGHT(CONVERT(VARCHAR,@NUMBER_DECIMAL_PART),LEN(CONVERT(VARCHAR,@NUMBER_DECIMAL_PART))-2)

    -- adjust the zero padding
    SET @DECIMAL_NUMBER_STR =left(@NUMBER_DECIMAL_PART_STR + REPLICATE('0', @DECIMAL_PLACES),@DECIMAL_PLACES)

    -- final result
   RETURN CONVERT(VARCHAR,@NUMBER_INT_PART) + '.' + @DECIMAL_NUMBER_STR

END

推荐答案

已由他人完成:

http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-format.html [

It has been done by others:

http://www.siccolo.com/Articles/SQLScripts/how-to-create-sql-to-format-currency.html[^]

Or try something like this:

CONVERT(varchar(10), CONVERT(money, @NUMBER))

http://articles.techrepublic.com.com/5100-10878_11-5803705.html [ ^ ]

祝你好运！

http://articles.techrepublic.com.com/5100-10878_11-5803705.html[^]

Good luck!

以任何方式使我的函数运行完美..并且我已经对其进行了修改..因为如果您传递整数，则上述错误将导致错误.
这是新的

any way i have the function is working perfectly.. and i have modified it.. because the above one will give error if you passed integer number.

here is the new one

CREATE FUNCTION [dbo].[UDF_FORMAT_NUMBER]
(
@NUMBER    FLOAT,
@DECIMAL_PLACES INT
)
RETURNS VARCHAR(500)
AS
BEGIN

    DECLARE @NUMBER_INT_PART BIGINT
    DECLARE @NUMBER_DECIMAL_PART FLOAT
    DECLARE @DECIMAL_NUMBER_STR VARCHAR(300)
    DECLARE @NUMBER_DECIMAL_PART_STR VARCHAR(300)

    -- GET INTEGER PART ONLY
    SET @NUMBER_INT_PART = CONVERT(BIGINT,@NUMBER)

    -- THIS WILL GIVE THE NUMBER AS FLOAT ALWAYS WILL BE 0.<DECIMAL_NUMBER>
    SET @NUMBER_DECIMAL_PART = @NUMBER - @NUMBER_INT_PART

    if @NUMBER_DECIMAL_PART>0
    begin
    -- NOW WE WILL GET THE DECIMAL_PART ALONE
        SET @NUMBER_DECIMAL_PART_STR = RIGHT(CONVERT(VARCHAR,@NUMBER_DECIMAL_PART),LEN(CONVERT(VARCHAR,@NUMBER_DECIMAL_PART))-2)
            -- adjust the zero padding
        SET @DECIMAL_NUMBER_STR =left(@NUMBER_DECIMAL_PART_STR + REPLICATE('0', @DECIMAL_PLACES),@DECIMAL_PLACES)
    end
    else
        SET @DECIMAL_NUMBER_STR = REPLICATE('0', @DECIMAL_PLACES)



    -- final result
   RETURN CONVERT(VARCHAR,@NUMBER_INT_PART) + '.' + @DECIMAL_NUMBER_STR

END

这篇关于格式化编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！