sql select comment requirement

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

sql select comment requirement

Author

Topic

Guruprasath.cub
Starting Member

8 Posts

Posted - 2012-03-29 : 10:14:01

In one of the column of the table the values (amount) ranging from 562500.00 to 8956251.00. When using select comment the amount 562500.00 should be represented as 0000562500 and 8956251 should be reported as 0008956251. That is the amount need to be padl with '0' on left side and total char should be 10. How can I get it through select comment ?

Guru

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-29 : 10:16:27

Guru,

Can you tell us WHY you need to do this in SQL and not displayed this way in a report or on the front end?

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Guruprasath.cub
Starting Member

8 Posts

Posted - 2012-03-29 : 10:24:46

Sir,
This is actually for a report. If I get the comment, I will use the same and get the output as TXT file. Could you please help me ?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-29 : 10:24:48

DECLARE @m decimal(15,2) = 562500.00
SELECT RIGHT(REPLICATE('0',10)+CONVERT(varchar(25),CONVERT(int,@m)),10)

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-29 : 10:25:49

Just curious though..doesn't your reporting tool allow you to do whatever formatting you want?

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

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Guruprasath.cub
Starting Member

8 Posts

Posted - 2012-03-29 : 10:36:06

Thank Brett. I will use the comment and revert back
Once again thanks for your guidance !

Guruprasath.cub
Starting Member

8 Posts

Posted - 2012-03-29 : 11:21:06

Brett !

I have used the following

select scheme, ac_type, country, currency,
right(replicate('0',10))+convert(varchar(25),convert(int,sum(amount)),10) as amount,
sum(no_ac) as no_ac from nrd_br_data where r_period=201111 and currency='AUD' group by scheme, country, currency order by scheme, country, currency

but it throws error message as 'The right function requires 2 arguments.'

Could you please come for help ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources