CODEWITHSUNDEEP
sql
necea0612
necea0612

Reputation: 9

Where <column name> is not null

This is the question: Select from the Person Table the BusinessEntityID, Title, the first letter of First Name, first letter of MiddleName, and Last Name (label as Full Name) where there isn’t NULL in any part of the name.

This is what I have thus far: 

 Use AdventureWorks2008R2;
    Select BusinessEntityID,SUBSTRING(Title,1,3)+' '+SUBSTRING(FirstName,1,1)+' '+
    SUBSTRING(MiddleName,1,1)+' '+ LastName as 'Full Name'
    From Person.Person
    Where LastName IS NOT NULL;

However the LastName column shows NULL values

Upvotes: 0

Views: 3587

Answers (2)

Michael Dunlap
Michael Dunlap

Reputation: 4310

If any of the columns you are concatenating together is NULL, the result will be NULL. You have to filter out Title, FirstName and MiddleName as well.

Use AdventureWorks2008R2;
SELECT BusinessEntityID,SUBSTRING(Title,1,3)+' '+SUBSTRING(FirstName,1,1)+' '+
SUBSTRING(MiddleName,1,1)+' '+ LastName as 'Full Name'
From Person.Person
Where LastName IS NOT NULL AND
      Title IS NOT NULL AND 
      FirstName IS NOT NULL AND
      MiddleName IS NOT NULL;

Upvotes: 1

waka-waka-waka
waka-waka-waka

Reputation: 1045

You can do something like:

Use AdventureWorks2008R2;
Select BusinessEntityID,SUBSTRING(Title,1,3)+' '+SUBSTRING(FirstName,1,1)+' '+
SUBSTRING(MiddleName,1,1)+' '+ LastName as 'Full Name'
From Person.Person
Where LastName IS NOT NULL AND LastName != ''

Upvotes: 0

Related Questions