Best way to write statement - 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 2000 Forums

SQL Server Development (2000)

Best way to write statement

Author

Topic

allend2010
Starting Member

28 Posts

Posted - 2003-07-11 : 11:14:27

Hello:

I am writing a sql statement that is parsing data to be output to a file. I have a money field that I need to pad with 0's for a length of 5.

For example:
3.75 needs to become 00375. Any good ideas on the easiest way to accomplish this.

Thanks,
Al

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-11 : 11:46:19

'00000' + CAST(moneyfield AS VARCHAR)

gives

'000003.75'

But you want only 5 digits so wrap it in

RIGHT ('00000' + CAST(moneyfield AS VARCHAR), 5)

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-11 : 11:48:25

How about


DECLARE @x decimal(3,2)

SELECT @x = 3.75

SELECT RIGHT(REPLICATE('0',5)+ CONVERT(varchar(5),CONVERT(int,@x*100)),5)

Brett

8-)

EDIT: Notice the use of REPLICATE doooh

Edited by - x002548 on 07/11/2003 11:51:37

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-11 : 11:50:43

Sam,

I think he want's to lose the decimal (why is heading up to the mainframe?)


DECLARE @x decimal(3,2)

SELECT @x = 3.75

SELECT RIGHT ('00000' + CAST(@x AS VARCHAR), 5)

Leaves the decimal in

Brett

8-)

allend2010
Starting Member

28 Posts

Posted - 2003-07-11 : 11:54:41

Sorry, yes I did want to leave the decimal out.

Thanks,
Al

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources