CODEWITHSUNDEEP
sqlsql-server-2008
evglynn
evglynn

Reputation: 327

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!

Upvotes: 1

Views: 653

Answers (3)

Michael Buen
Michael Buen

Reputation: 39393

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', '[email protected]'),
('James Paul','McCartney', null),
('George','Harrison', '[email protected]'),
('Ringo','Starr', '[email protected]');


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

Upvotes: 4

Mike Calvert
Mike Calvert

Reputation: 131

The simplest way I accomplish this is by using the ISNULL statment. I will typically write out ISNULL(Field,' ') or whatever value I would pass across for a null such as N/a. This ISNULL command takes in a value and returns the second parameter if the value is null otherwise it returns the value of the first parameter.

Upvotes: 0

Brad Christie
Brad Christie

Reputation: 101604

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

What you're looking for is COALESCE (and REPLICATE to make your life easier)

Alternatively you can CONCAT and LEFT it:

SELECT LEFT(CONCAT( Title,        REPLICATE(' ',10) ), 10) AS Title,
       LEFT(CONCAT( FirstName,    REPLICATE(' ',75) ), 75) AS FirstName,
       LEFT(CONCAT( LastName,     REPLICATE(' ',75) ), 75) AS LastName,
       LEFT(CONCAT( EmailAddress, REPLICATE(' ',100) ), 100) AS FirstName
FROM   AdventureWorks.Person.Contact

Upvotes: 0

Related Questions