Formating Currecny Number

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

SQL

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

Posted 4-Aug-10 20:06pm

hussain.attiya

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2010-08-04T21:50:00

Solution 1

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:

SQL

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

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

Good luck!

Posted 4-Aug-10 21:50pm

E.F. Nijboer

Comments

hussain.attiya 5-Aug-10 3:54am

thanks .. i have seen the function.. but it is not what i asked about.
it is only formating a currecny number.

what i asked about is to format a number by passing it and another parameter (decimal places) count

hussain.attiya 5-Aug-10 3:56am

Reason for my vote of 3
not what i have asked about

E.F. Nijboer 5-Aug-10 4:26am

Well, I tried my best :) but I did found somewhat strange to round currency that way. But luckely you found the solution you needed.

hussain.attiya · Answer 2 · 2010-08-04T21:58:00

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

SQL

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