Right Justify a character field

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

Right Justify a character field

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-05 : 15:44:11

Hi,
Is there a simple way to right justify a character field.
For ex: I have a field AGE defined as char(3), with 2 possible values 9 and 20

In the output I want it to be as ' 9' and ' 20'.
I have done it in this way

RIGHT((' ' + AGE),3).

I have a whole bunch of fields like this. SO wanted to know if there is a better way?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-05 : 15:59:53

Its more a reporting issue. Better if you handle it on the report end.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-05 : 16:02:24

There is no reporting end. The table is just downloaded to a file. Its a staging table. So whatever formatting I need to do, it shud be done on this staging table.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-05 : 17:17:10

Then there is no better way.
For better readability you can use SPACE(3) instead of

'   '

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-05 : 17:38:07

What you have won't work anyway for a char(3) column.


DECLARE @t TABLE
(
	AGE	CHAR(3)
)

INSERT @T VALUES('9')
INSERT @T VALUES('20')


SELECT '''' + AGE + '''' [Age] , '''' + RIGHT(SPACE(3) + RTRIM(AGE), 3) + '''' [Justified_Age] FROM @t

Results:
Age	Justified_Age
'9  '	'  9'
'20 '	' 20'

(The quotes were added for visibility)

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-05 : 17:51:49

Why would AGE be char(3)

Better yet, why wouldn't it be calculated from GetDate() and BirthDt??

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-02-06 : 10:24:08

quote:
Originally posted by vijayisonly

There is no reporting end. The table is just downloaded to a file.

What's the file format? Fixed width?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-06 : 11:47:27

why not do this


SELECT RIGHT('          ' + RTRIM([yourCol]), x)

you don't need to mess with SUBSTRING only value you have to worry about is NULL.


DECLARE @foo TABLE (
	[col] VARCHAR(50)
	)
INSERT @foo
      SELECT ''
UNION SELECT '2'
UNION SELECT '112 '
UNION SELECT 'bang   '
UNION SELECT 'bang'
UNION SELECT NULL
UNION SELECT 'foo bar  '

SELECT [col] FROM @foo AS [Plain]
SELECT RIGHT(SPACE(255) + ISNULL(RTRIM([col]),''), 10) AS [Right] FROM @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-06 : 14:23:19

Ok...I will incorporate some of the suggestions...but the real pain is to do field by field. I guess there is no easy way out of this. And to answer blindman's question...yes its a fixed width file

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 07:58:16

quote:
Originally posted by vijayisonly

Hi,
Is there a simple way to right justify a character field.
For ex: I have a field AGE defined as char(3), with 2 possible values 9 and 20

In the output I want it to be as ' 9' and ' 20'.
I have done it in this way

RIGHT((' ' + AGE),3).

I have a whole bunch of fields like this. SO wanted to know if there is a better way?

]
Try

STR(AGE,3)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources