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 |
|
rajnidas
Yak Posting Veteran
97 Posts |
 Posted - 2015-04-22 : 01:21:39
|
| Hi Everyone, I need below column amount should be starts from right to left.please need help.Outstanding_balance-------------------------98006735.6598006735.65129571600000750865result should be as below---------- 98006735.65 98006735.65 12957 1600000 750865Thanks & regardsRajnidas |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
rajnidas
Yak Posting Veteran
97 Posts |
Posted - 2015-04-22 : 01:47:43
|
| HI, Outstanding_balance AMOUNT COLUMN SHOULD BE STARTS FROM RIGHT TO LEFT result should be as below---------- 98006735.65 98006735.65 12957 1600000 750865REGARDSRAJNIDAS |
 |
|
|
rajnidas
Yak Posting Veteran
97 Posts |
Posted - 2015-04-22 : 01:48:53
|
| Posted - 04/22/2015 : 01:47:43 Show Profile Email Poster Edit Reply Reply with Quote Delete ReplyHI, Outstanding_balance AMOUNT COLUMN SHOULD BE STARTS FROM RIGHT TO LEFT , NEED SPACE BEFORE AMOUNT .result should be as below----------98006735.6598006735.65129571600000750865REGARDSRAJNIDAS |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-22 : 07:15:08
|
In case it helps there were spaces in the O/P's original that didn't show up:Outstanding_balance-------------------------98006735.6598006735.65129571600000750865result should be as below---------- 98006735.65 98006735.65 12957 1600000 750865 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-22 : 07:26:09
|
This is MUCH better done in the Application than in SQL, but you can do:SELECT RIGHT(REPLICATE(' ', 20) + CONVERT(varchar(20), Outstanding_balance), 10)the trouble with this is that you have converted a Float/Decimal/whatever into a String and anything else that wants to process that data will treat it as a string. It won't sort correctly, and it may well be incorrectly converted back to a number etc. by things downstream. (Dates, for example, are notorious for being wrongly handled/sorted/parsed when converted from Date/Time datatype to String)There's another potential pitfall too : If your Outstanding_balance has more digits that the "conversion" size that you use you will get NO error, the MOST SIGNIFICANT DIGIT will just be silently removed. That would, most likely, be catastrophic for the business!! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2015-04-22 : 07:27:59
|
SELECT STR(Outstanding_Amount, 20, 2)
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-22 : 07:30:22
|
quote:
Originally posted by SwePeso
SELECT STR(Outstanding_Amount, 20, 2)
Aligns left if number precision exceeds the size providedClearly "a good thing" but, sorry, "does not meet the spec"  (Well ... not as I read the spec  ) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-22 : 07:32:11
|
Actually spec is vague Outstanding_balance------------------------- <-- 25 chars98006735.65result should be as below---------- <-- 10 chars 98006735.65 <-- 18 chars |
|
|
|
|
|
|
|
|
column adjustment in sql