Output Field Length

[RichardParry]

Hi All,

I think this should be my last query in relation to a recent export I am trying to do from a DB in T-SQL. What I need to do is within my SELECT output is specify the character length of some columns. For example, although the data in the column may only be 4 characters I need to output the 4 characters of data, plus add spaces on the end, to equal a set number of characters, e.g. 8. So for example, my normal output that would be "TEXT", which is 4 chars would need look like "TEXT " as 8 characters, with spaces on to fill the blanks - if that makes sense. Never tried this before so could do with a bit of help please

There are about 9 columns of data I need to format like this, some using a variable set to a number of characters earlier on in the view, and some are simply hard-coded.

Thanx again!!!

 

[LNBruno]

something like this perhaps:

select 'text' + space(4)  --where 'text' would be columnname

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[gmmastros]

the char data type is padded on the right with spaces. Take a look at this... (Copy/paste to a query analyzer window).

Declare @Temp table(id int identity(1,1), EyeColor VarChar(10), ShoeSize numeric(3,1))

Insert Into @Temp Values('Blue', 9.5)
Insert Into @Temp Values('Purple', 8.0)

[green]-- Unpadded[/green]
Select * From @Temp

[green]-- Padded with spaces.[/green]
Select Convert(char(5), id) + Convert(Char(20),eyecolor) + Convert(Char(5), ShoeSize)
From   @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

George is right, but if you ever need to do this for some other character besides space:

select field1 + replicate ('x', 8-datalength(@field1)) from table1

"NOTHING is more important in a database than integrity." ESquared