Right align output of Select statement

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 2000 Forums

Transact-SQL (2000)

Right align output of Select statement

Author

Topic

ptang
Starting Member

4 Posts

Posted - 2004-10-18 : 06:10:28

Hi,

The SQL statement

 SELECT BookCode, Title,
        convert(decimal(8,2),(Price * 0.85)) as DiscountedPrice
 FROM Book;

outputs DiscountedPrice as left aligned values.

I tried

  SELECT BookCode, Title,
         right(convert(decimal(8,2),(Price * 0.85)),12)
           as DiscountedPrice
  FROM Book;

but it still outputs DiscountedPrice as left aligned values.

How can I output a column as numeric right aligned values?

Thanks
--ptang

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-18 : 06:14:44

right(space(12) + convert(varchar(12),convert(decimal(8,2),(Price * 0.85))),12)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-10-18 : 06:14:45

format it in the presentation layer.

Hemanth Gorijala
BI Architect / DBA...(yuhoo!!! and now, an "Yak Master")

Exchange a Dollar, we still have ONE each.
Exchange an Idea, we have TWO each.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-18 : 09:13:16

NEVER format values like this in SQL.

- Jeff

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-18 : 11:11:56

I do it when I'm creating text files for export.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ptang
Starting Member

4 Posts

Posted - 2004-10-18 : 11:13:51

right(space(12) + convert(varchar(12),convert(decimal(8,2),(Price * 0.85))),12)

This code works correctly.

Thank you for the suggestion.

--ptang

ptang
Starting Member

4 Posts

Posted - 2004-10-18 : 11:23:06

[code]right(space(12) + convert(varchar(12),convert(decimal(8,2),(Price * 0.85))),12)[/code]
How can the above formatting be performed
using the presentation layer or
or without this lengthy format construct.

Thanks
--ptang

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 11:31:20

well that depends on what's your presentation layer

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-18 : 11:33:57

ok -- text files for export is ok. that's about all !

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-18 : 11:40:52

dr. no, no. it's not ok.
for exporting data to text files you must build at least a 3 tier app with at least one web service...

Go with the flow & have fun! Else fight the flow

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-18 : 11:51:30

quote:
Originally posted by ptang

right(space(12) + convert(varchar(12),convert(decimal(8,2),(Price * 0.85))),12)

How can the above formatting be performed
using the presentation layer or
or without this lengthy format construct.

Thanks
--ptang

You can use that sort of construct in most languages.
t-sql gives few tools for formatting.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources