Q:

Accounting for Nulls/Convert into Spaces of 'x' length

SELECT
CONVERT(CHAR(10), Title)
+ CONVERT(CHAR(75), FirstName)
+ CONVERT(CHAR(75), LastName)
+ CONVERT(CHAR(100), EmailAddress)
FROM
AdventureWorks.Person.Contact 

If a value is null, how would I accomodate that in this situation?
My ultimate goal: create a fixed length output. 
Ideally: if its a null value, make is white space of that length specified.
Thanks in advance!

A:

You can also use the CAST CHAR technique instead of padding string by replicate:
create table Person
(Firstname varchar(50) not null,
 Lastname varchar(50) not null,
 Email varchar(50));

insert into Person values
('John Winston','Lennon', 'jwl@beatles.com'),
('James Paul','McCartney', null),
('George','Harrison', 'g@beatles.com'),
('Ringo','Starr', 'rstarkey@beatles.com');

select 
Fullname = 
     CAST(Lastname, AS CHAR(50))
     + CAST(Firstname AS CHAR(50))
     + CAST(COALESCE(Email,'') AS CHAR(50)) 
from person

http://www.sqlfiddle.com/#!3/bb771/1
Note, SQL Fiddle don't have error nor the query above, it's just that browsers render multiple spaces into one, you can see it's 50 characters by using Inspect Element(if you are using Google Chrome) on the grid. The display has 50 characters on each CAST
Of course you can also use CONVERT http://www.sqlfiddle.com/#!3/bb771/3
select 
    Fullname = 
        CONVERT(CHAR(50), Lastname)
        + CONVERT(CHAR(50), Firstname)
        + CONVERT(CHAR(50), COALESCE(Email,'')) 
from person

Of course you can also use ISNULL http://www.sqlfiddle.com/#!3/bb771/4
select 
    Fullname = 
        CONVERT(CHAR(50), Lastname)
        + CONVERT(CHAR(50), Firstname)
        + CONVERT(CHAR(50), ISNULL(Email,'')) 
from person

Just to verify things are working as intended, visualize space:
with a as
(   
    select 
        Fullname = 
            CONVERT(CHAR(50), Lastname)
            + CONVERT(CHAR(50), Firstname)
            + CONVERT(CHAR(50), COALESCE(Email,''))  
    from person
) 
select replace(Fullname,' ','.') from a

Lennon............................................John.Winston......................................jwl@beatles.com...................................
McCartney.........................................James.Paul..........................................................................................
Harrison..........................................George............................................g@beatles.com.....................................
Starr.............................................Ringo.............................................rstarkey@beatles.com..............................

http://www.sqlfiddle.com/#!3/bb771/8
Another tip, any performance-killer, e.g. unnecessary function call should be avoided, to make your queries fast. In this case, if Firstname and Lastname rejects NULL on inputs, no need to put COALESCE or ISNULL around them, in this case, we just put COALESCE or ISNULL on Email

