Comma Separator values

[khshankar]

I have written a query in SQL Server 2000. I am showing the sum, I want the sum to be comma separated (eg., 100,000,000)
How to get in this format.

And also I am calculating percentage, some values has 2 digits before decimal place and some has 1 digit before decimal place. If it has 1 digit before decimal place I want to add a '0' before that. (eg., 9.24% --- 09.24%). How to get this.

What format I have to use to get these results.

 

[SQLBill]

For the first question, the easiest way would be to CONVERT to MONEY using STYLE 1.

SELECT CONVERT(MONEY, '1234567.89', 1)

Refer to the BOL for more information on CONVERT and MONEY.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
or online at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

Posting advice: FAQ481-4875

 

[SQLBill]

Also, check out FAQ183-4418 by MrDenny. It's a script on how to format numbers (commas and decimals).

-SQLBill

Posting advice: FAQ481-4875

 

[amrita418]

One of the options to get what you want for question 2.

declare 
@v_string varchar(10)
set @v_string = '9.24%'
select case when(len(substring(@v_string, 1, charindex('.', @v_string) - 1))) = 1 then  '0' + @v_string else 
@v_string end

Regards,
AA

 

[SQLBill]

Or try this:

DECLARE @myvalue VARCHAR(5)
SET @myvalue = '1.23'
IF (SELECT CHARINDEX('.', @myvalue)) < 3
 BEGIN
   SELECT '0' + @myvalue
 END
ELSE
 BEGIN
    SELECT @myvalue
 END

However, if your value being inputted is not CHAR or VARCHAR, you will have to CONVERT it in the first SELECT or it will try to ADD it instead of CONCATINATING it.

-SQLBill

Posting advice: FAQ481-4875