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
 General SQL Server Forums
 New to SQL Server Programming
 How to display..

Author  Topic 

raky
Aged Yak Warrior

767 Posts
Posted - 2008-04-21 : 02:21:49
Hi,

I need to show the value of a variable with comma seperated.

For eg: I have a variable N whose value is 123456.89
I need to display it as 1,23,456.89
How i can i display this...
Please help me

Thanks in advance

pravin14u
Posting Yak Master

246 Posts
Posted - 2008-04-21 : 02:44:39
where do you want to display this?

Are you using any reporting tools?

Prakash.P
The secret to creativity is knowing how to hide your sources!
Go to Top of Page

raky
Aged Yak Warrior

767 Posts
Posted - 2008-04-21 : 02:57:22
quote:
Originally posted by pravin14u

where do you want to display this?

Are you using any reporting tools?

Prakash.P
The secret to creativity is knowing how to hide your sources!


I want to display in the listing grid of a screen.I want to do it from backend
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-04-21 : 03:22:43
Why would you like to do it the hard way when formatting is so much easier in the front-end?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-04-21 : 05:27:52
quote:
Originally posted by raky

quote:
Originally posted by pravin14u

where do you want to display this?

Are you using any reporting tools?

Prakash.P
The secret to creativity is knowing how to hide your sources!


I want to display in the listing grid of a screen.I want to do it from backend

What is your front end?
If You use front end application, use format function there

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

raky
Aged Yak Warrior

767 Posts
Posted - 2008-04-22 : 06:16:10
Iam a database programmer. coder in our team asked me to do that in backend. so i need to do it in backend. Please help me..
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-04-22 : 08:50:08
quote:
Originally posted by raky

Iam a database programmer. coder in our team asked me to do that in backend. so i need to do it in backend. Please help me..

Tell them that it should be done in front end application
If there is no option,

Select convert(varchar(50),col,1) from table

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2008-04-22 : 09:21:28
Your coder should know how to format a number!! My goodness, this is absolutely something that should NOT be done in a database! If you "format" the number in the database, you are now returning a VARCHAR which the front end code cannot sort, compare, do math, custom format, and so on.

Explain to your coder that it is HIS JOB to format data coming from the database at the front end. That's literally what he is supposed to be doing when writing a front end. If he can't or won't do it, then you need to get a new coder. You are making his job easier for him by returning raw numbers that he can work with in his code, not returning pre-formatted strings.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2008-04-22 : 10:36:48
quote:
Originally posted by jsmith8858

Your coder should know how to format a number!! My goodness, this is absolutely something that should NOT be done in a database! If you "format" the number in the database, you are now returning a VARCHAR which the front end code cannot sort, compare, do math, custom format, and so on.

Explain to your coder that it is HIS JOB to format data coming from the database at the front end. That's literally what he is supposed to be doing when writing a front end. If he can't or won't do it, then you need to get a new coder. You are making his job easier for him by returning raw numbers that he can work with in his code, not returning pre-formatted strings.

- Jeff
http://weblogs.sqlteam.com/JeffS



What's the big deal?


DECLARE @x money
SELECT @x = 123456.89
SELECT convert(varchar(25),@x,1)




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2008-04-22 : 10:39:52
Or more precisely


DECLARE @x money
SELECT @x = +922337203685477.5807
SELECT convert(varchar(27),@x,1)



But you'll lose 2 decimal places



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-04-22 : 10:40:58
quote:
Originally posted by raky

I need to display it as 1,23,456.89
I hope this is a type then...


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2008-04-22 : 10:45:05
quote:
Originally posted by Peso

quote:
Originally posted by raky

I need to display it as 1,23,456.89
I hope this is a type then...


E 12°55'05.25"
N 56°04'39.16"


Looks like it's Courier New



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2008-04-22 : 11:38:51
quote:
Originally posted by X002548

quote:
Originally posted by jsmith8858

Your coder should know how to format a number!! My goodness, this is absolutely something that should NOT be done in a database! If you "format" the number in the database, you are now returning a VARCHAR which the front end code cannot sort, compare, do math, custom format, and so on.

Explain to your coder that it is HIS JOB to format data coming from the database at the front end. That's literally what he is supposed to be doing when writing a front end. If he can't or won't do it, then you need to get a new coder. You are making his job easier for him by returning raw numbers that he can work with in his code, not returning pre-formatted strings.

- Jeff
http://weblogs.sqlteam.com/JeffS



What's the big deal?


DECLARE @x money
SELECT @x = 123456.89
SELECT convert(varchar(25),@x,1)




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Are you kidding?

Do I really have to explain the pros and cons of returning pre-formatted VARCHARs to your clients versus actual numeric values?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

raky
Aged Yak Warrior

767 Posts
Posted - 2008-04-22 : 11:48:44
quote:
Originally posted by X002548

quote:
Originally posted by Peso

quote:
Originally posted by raky

I need to display it as 1,23,456.89
I hope this is a type then...


E 12°55'05.25"
N 56°04'39.16"


Looks like it's Courier New


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam






Hi,

what i need is still i didn't get. I need to display the value of a variable as 1,12,234.56 if its original value is 112234.56. Please guide me in that direction. Any way thanks to all for their efforts...
I hope my problem will be solved soon with your help..
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2008-04-22 : 11:53:54
Raky -- do you understand that the only way to "format" a number in T-SQL is to CONVERT it to a VARCHAR? At that point, it is no longer a number -- it is just a meaningless string that cannot be sorted, compared, calculated, etc.

As I said, it is extremely easy and trivial for your front-end coder to format numbers. Usually by specifying a simple format string. If you try to do this at the database, your code is longer, less flexible, and less efficient.

Does this make any sense?

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2008-04-22 : 12:15:13
quote:
Originally posted by jsmith8858

Raky -- do you understand that the only way to "format" a number in T-SQL is to CONVERT it to a VARCHAR? At that point, it is no longer a number -- it is just a meaningless string that cannot be sorted, compared, calculated, etc.

As I said, it is extremely easy and trivial for your front-end coder to format numbers. Usually by specifying a simple format string. If you try to do this at the database, your code is longer, less flexible, and less efficient.

Does this make any sense?

- Jeff
http://weblogs.sqlteam.com/JeffS



Yes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-04-22 : 14:04:31
Raky,

What are the formatting rules for formatting a number? Formatting 123456.89 as 1,23,456.89 is not a simple conversion. It implies that you have custom formatting rules.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-04-22 : 14:28:09
quote:
Originally posted by X002548

Looks like it's Courier New



My bad (and typo on my behalf). I meant to write typo in previous response.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-04-23 : 02:31:07
quote:
Originally posted by Lamprey

Raky,

What are the formatting rules for formatting a number? Formatting 123456.89 as 1,23,456.89 is not a simple conversion. It implies that you have custom formatting rules.

That formation is specific to Indian way of formatting the currency

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-04-23 : 02:32:06
quote:
Originally posted by Peso

quote:
Originally posted by X002548

Looks like it's Courier New



My bad (and typo on my behalf). I meant to write typo in previous response.



E 12°55'05.25"
N 56°04'39.16"

Yes I understood it must be typo

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-04-23 : 11:58:14
quote:
Originally posted by madhivanan

quote:
Originally posted by Lamprey

Raky,

What are the formatting rules for formatting a number? Formatting 123456.89 as 1,23,456.89 is not a simple conversion. It implies that you have custom formatting rules.

That formation is specific to Indian way of formatting the currency

Madhivanan

Failing to plan is Planning to fail
Right on. I had not seen that before. I had one of the guys I work with explain how and why the number is formatted that way. Interesting. :)
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site