align right number converted to char

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

align right number converted to char

Author

Topic

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-10 : 22:33:20

Hello,

I would like to convert a number to a string and to align it right.

Convert(char(5), 1) will return "1 " whereas I would like to get " 1". Is it possible?

Thank you in advance for any help,

Best regards,

mathmax

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-10 : 22:53:50

[code]select right(' ' + convert(varchar(5), 1), 5)[/code]

KH
[spoiler]Time is always against us[/spoiler]

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-11 : 08:10:46

Thank you for your answer.

Your code do not exactly what I want.

I would like to to get the following results:

12 -> ' 12'
1 -> ' 1'
1.2 -> ' .2'
1212 -> ' 1212'

Is it possible ?

regards,

mathmax

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-11 : 08:13:47

SELECT ' ' + CAST(Yourfield as varchar(10))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-11 : 08:25:42

quote:
Originally posted by mathmax

Thank you for your answer.

Your code do not exactly what I want.

I would like to to get the following results:

12 -> ' 12'
1 -> ' 1'
1.2 -> ' .2'
1212 -> ' 1212'

Is it possible ?

regards,

mathmax

Is that a type error?

Madhivanan

Failing to plan is Planning to fail

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-11 : 08:32:14

Sorry but this will give the same result.

I don't want a fix number of white spaces to the left. I want to complete with white space so that the total length of the string will be always 5.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-11 : 08:34:00

quote:
Originally posted by mathmax

Sorry but this will give the same result.

I don't want a fix number of white spaces to the left. I want to complete with white space so that the total length of the string will be always 5.

What is your output for the following?

1234.56
0.873333
890223
78234.78

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-11 : 08:34:43

SELECT STR(Col1, 4)
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-11 : 09:22:32

You should simply format and align your output at your presentation layer (reporting tool, web page, Excel file, etc). It is easy to do and the standard way to handle formatting. Converting all of your data to strings not a good idea.

- Jeff
http://weblogs.sqlteam.com/JeffS

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-11 : 09:51:11

below might help you--

declare @str varchar(5)
set @str = '12'
set @str = LTRIM(RTRIM(@str))
select REPLICATE(' ',5-LEN(@str)) + @str

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-07-11 : 19:44:02

Thank you.

That's exactly what I need.