How do I Return Numeric Data with a Comma Thousands Separator in SQL Server

Posted on 2006-05-22
Last Modified: 2012-06-27
I am trying to display a number with commas in an SQL Server 2000 View. I've read several solutions on various websites, but none are working for me.

Input: 1234567.89
Desired output: 1,234,567 (I don't want any decimal places).

I have tried:

REPLACE(CONVERT(VARCHAR(20), CAST(ROUND(dbo.table1.area, 0) AS MONEY), 1), '.00', '')

But I keep getting an SQL Server Enterprise Manager error window with "Data type error in expression".

I tried

REPLACE(cast (CONVERT(VARCHAR(20), CAST(ROUND(dbo.table1.area, 0) AS MONEY), 1) as VARCHAR, '.00', '')

with the same result.  Geez, it was so easy in Cobol !!!!!!

Question by:berniepet
    LVL 39

    Expert Comment

    try something like this

    declare @val numeric(10,2);
    select @val=123456.44;
    select @val, convert(varchar,convert(money,'$' + convert(varchar,@val)),128)

    so your case it should be something like this
    convert(varchar,convert(money,'$' + convert(varchar, dbo.table1.area)),128)
    LVL 3

    Accepted Solution

    create function dbo.myRound(@val decimal(30,2))
    returns varchar(500)
    declare @rounded decimal(30,2)
    set @rounded = round(@val,0)
    declare @return varchar(500)
    set @return = convert(varchar,cast(@rounded as money),1)
    set @return = left(@return,len(@return)-3)
    return @return
    select dbo.myRound(1234567.89)
    --returns 1,234,568  ***notice the last char is an 8 due to rounding up

    If you don't want it to round up like that replace the set @rounded line with

    set @rounded = @val

    You can then call this function from your query as in select dbo.myRound(colname)..... from ....
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Have you considered using your front-end application to do this?

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now