You can CAST (or CONVERT) like in this example:

SELECT 
CAST(fname AS CHAR(30)) + CAST(lname AS CHAR(30))
, RIGHT('0000000000' + CAST(job_Id AS VARCHAR), 10)
, emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date 
FROM pubs.dbo.employee

>I have a requirement using SQL Server 2000 to do the following:
>
>1. Create a character field that has a specified maximum length, left justified and if necessary padded with SPACE’s to reach the maximum field length.
>
>2. Create a numeric field that has a specified maximum length, right justified and if necessary padded with ZERO’s to reach the maximum field length.
>
>In any case there may be no or some data in a given field so padding is necessary to obtain the correct position of data when it is extracted.
>
>This is the first time I have had to take data from SQL Server, convert it to a text file to be uploaded to a Unix machine. I have done so many conversions of various file formats using products like data Junction. This is an interesting twist for me.
>
>Thank you for any ideas.
>
>Tom