Formatting a selected value - 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

Formatting a selected value

Author

Topic

SexyChick
Starting Member

6 Posts

Posted - 2006-01-25 : 13:21:16

I am new to writing sprocs so forgive me if this is trivial. I am selecting fields from a table and placing them into a temp table in row format. (Row 1 in temp table is the first row in a file that will be created using DTS package). My question is: How can a format a field that I have selected that only has, say 3 chars, into a value of 5.
Ex: field in DB = aaa
I need to format it as: 2 spaces + aaa

But the length of the value will be varying from record to record.

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-25 : 13:52:34

[code]
DECLARE @xxx varchar(3)
SELECT @xxx = 'aaa'
SELECT '"12345"' UNION ALL
-- Just use this statememt with out the double quotes
SELECT '"'+RIGHT(REPLICATE(' ',5)+@xxx,5)+'"'

[/code]

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

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-25 : 13:53:39

Try
Select REPLICATE(' ', 2 - DATALENGTH(CAST(F1 AS VARCHAR))) + convert(varchar, F1) from Tbl

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2006-01-25 : 15:02:19

Or this


DECLARE @abc varchar(3)
SET @abc = 'abc'

SELECT REVERSE(CONVERT(char(5),REVERSE(@abc)))

Beauty is in the eyes of the beerholder

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-25 : 15:03:20

Andy's started drinking early today

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

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2006-01-25 : 15:04:40

I have, [hic] bottle of Becks in hand.....

Its been a while that i forgot how to post!

Beauty is in the eyes of the beerholder

SexyChick
Starting Member

6 Posts

Posted - 2006-01-25 : 15:27:55

Thanks Guys!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 01:11:15

One more method


DECLARE @abc varchar(3)
SET @abc = 'abc'
select space(5-len(@abc))+@abc

Andy, Where were you for long time?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources