seperate with comma - 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 2005 Forums

Transact-SQL (2005)

seperate with comma

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2009-01-16 : 14:39:41

Hi,

I am getting the output of one column in this foramt

(100000) i need this in this foramt (100,000)

one more ex: 1000000 to 1,000,000

Can ony one help me

dev

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-01-16 : 14:42:55

This should be done at the application/presentation layer not within TSQL. Where are you trying to do this?

Terry

-- Procrastinate now!

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-16 : 14:46:27

When you add the commas to a number it will have to be converted to a varchar or other string format. You will lose all capability of doing any manipulations of those numbers such as aggregation, etc. Like toss said, formatting should be done through the application layer rather than SQL.

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2009-01-16 : 16:19:42

When i have to display the output should be like this
100,000

Is there any possibility to do that?

quote:
Originally posted by Skorch

When you add the commas to a number it will have to be converted to a varchar or other string format. You will lose all capability of doing any manipulations of those numbers such as aggregation, etc. Like toss said, formatting should be done through the application layer rather than SQL.

dev

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-01-16 : 16:49:19

try this


declare @test as decimal 
set @test = 894545645654 
select @test, left(cast(convert(varchar(20),cast(@test as money),1) as 
varchar), len(convert(varchar(20),cast(@test as money),1)) -3)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-16 : 23:06:16

try this too
select substring(convert(varchar(20),cast(@test as money),1),1,charindex('.',convert(varchar(20),cast(@test as money),1),1)-1)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-16 : 23:12:13

Try this also,

declare @test as decimal
set @test = 894545645654
select PARSENAME(CONVERT(VARCHAR(20),CAST(@test AS MONEY),1),2)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 23:28:44

quote:
Originally posted by sqldev6363

When i have to display the output should be like this
100,000

Is there any possibility to do that?

quote:
Originally posted by Skorch

When you add the commas to a number it will have to be converted to a varchar or other string format. You will lose all capability of doing any manipulations of those numbers such as aggregation, etc. Like toss said, formatting should be done through the application layer rather than SQL.

dev

whats the front end application you're using?

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-20 : 14:48:50

So how would you format the number for display on the front end?

Thanks for the help
CoachBarker

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2009-01-20 : 19:26:27

Thanks alot, got it. Its working

quote:
Originally posted by bklr

try this too
select substring(convert(varchar(20),cast(@test as money),1),1,charindex('.',convert(varchar(20),cast(@test as money),1),1)-1)

dev

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-20 : 19:31:03

So what was the solution to formatting at the front end.

Thanks for the help
CoachBarker

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-20 : 19:33:39

quote:
Originally posted by CoachBarker

So what was the solution to formatting at the front end.

Thanks for the help
CoachBarker

It depends on what programming language you are using.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-20 : 21:02:24

We are using SSRS 2005 reporting services in Visual Basic 2.0

Thanks for the help
CoachBarker

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-21 : 02:51:29

quote:
Originally posted by CoachBarker

We are using SSRS 2005 reporting services in Visual Basic 2.0

Thanks for the help
CoachBarker

There should be a format function something like FORMAT(col,"###,###.00"). Refer the help file for the exact syntax

Madhivanan

Failing to plan is Planning to fail

CoachBarker
Posting Yak Master

170 Posts

Posted - 2009-01-21 : 05:41:57

Now we do not want .00 at the end, these are weights that we are talking about so it would be 000,000 or 00,000.

Thanks for the help
CoachBarker

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 08:59:27

quote:
Originally posted by CoachBarker

We are using SSRS 2005 reporting services in Visual Basic 2.0

Thanks for the help
CoachBarker

then you dont have to use any format function. just right click on cell displaying the value and choose format tab and type number, you've a standard format available with commas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources