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.
| Author |
Topic |
|
yakoo
Constraint Violating Yak Guru
312 Posts |
 Posted - 2001-11-29 : 11:03:42
|
| I have a situation where I need to output in a txt file a right justified money field that has a percision of 2.Some of the records in the Database have a percision of 4. Is there a way in SQL to return this value wither a percision of only 2?Another question is to pad this field for right justification. I basically am looking to do the following. 1) Convert money to char(12) but put leading spaces instead of trailing spaces.2) When converting money to char keep standard money formating such as commas and $.Any help is appreciated |
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2001-11-29 : 11:12:37
|
| I got most of this to work already after some searching in BOL and The Guru's Guide To SQL.the STR function works great for the right justification with the rounding of the decimal. Now to attempt the money format issue |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-11-29 : 11:25:54
|
| Just put a dollar sign in front of it:SELECT '$' + Str(moneyCol, 8,2) FROM myTableThe CONVERT function also has some format options for money values:SELECT '$' + Convert(char(8), moneycol, 2) FROM myTableCheck Books Online, look after the date format codes and you'll find the numeric format codes. |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2001-11-29 : 11:34:23
|
I can't really do either of those. If I do str and moneyCol has a length of 4 my output will be '$ 2.34' instead of ' $2.34' like I need. I would like to do the convert becuase it will give me $2.34 but then I would need to determine padding. Is there a function that will pad to the beginning of a string?sorry about the edit. damn HTML and spacesEdited by - yakoo on 11/29/2001 11:36:38 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2001-11-29 : 11:42:11
|
| You can use the STUFF() function to insert the $ character, this might work:SELECT STUFF(Str(moneyCol,8,2), 8-Len(LTrim(Str(moneyCol,8,2))), 1, '$') FROM myTableYou might need to adjust the position values in the Stuff() function to get this exactly right. |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2001-11-29 : 12:09:32
|
| the stuff function combined with STR worked like a charm. Thanks Rob |
|
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2001-11-29 : 12:50:30
|
| I ended up creating a user defined function that takes a string and desired length. This function then left pads the string with spaces.RIGHT(SPACE(@length) + @string, @length) |
|
|
|
|
|
|
|
|
Money Format for Output