How to format numbers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to format numbers

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2011-09-08 : 02:12:18

Hi,

Would like to know how to format a number from this format: 11000 to this format: 11,000 and if the value is negative, the format will appear like this: (11,000)

I had tried the method from this link but it's not working:
http://www.baycongroup.com/learning_sql.htm

Please advise.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-09-08 : 05:58:04

is it Some thing like This.....?


create table #temp
(
num int
)
GO

insert into #temp values(11000 )
insert into #temp values(-11000 )
GO

select 
	 case 
		when num>=0 
		then left(convert(varchar(30), cast(num as money),1),charindex('.',convert(varchar(30), cast(num as money),1))-1) 	
		Else '('+left(convert(varchar(30), cast(num*(-1) as money),1),charindex('.',convert(varchar(30), cast(num*(-1) as money),1))-1)+')' 	
		End as fromatnum		   
from #temp

Result:

fromatnum
11,000
(11,000)

--Ranjit

Sachin.Nand

2937 Posts

Posted - 2011-09-08 : 06:12:39

Why do you want to do it in SQL ? It is a very bad idea.But if you really wanna to do it then its better you create a CLR procedure to format the data.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources