column adjustment in sql - 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

General SQL Server Forums

New to SQL Server Programming

column adjustment in sql

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.65
98006735.65
12957
1600000
750865

result should be as below
----------

98006735.65
98006735.65
12957
1600000
750865

Thanks & regards

Rajnidas

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-22 : 01:33:09

What do you mean by right to left?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

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
750865

REGARDS

RAJNIDAS

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 Reply

HI, Outstanding_balance AMOUNT COLUMN SHOULD BE STARTS FROM RIGHT TO LEFT , NEED SPACE BEFORE AMOUNT .

result should be as below
----------

98006735.65
98006735.65
12957
1600000
750865

REGARDS

RAJNIDAS

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.65
98006735.65
12957
1600000
750865

result 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 provided

Clearly "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 chars
98006735.65

result should be as below
---------- <-- 10 chars
       98006735.65  <-- 18 chars

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources