CODEWITHSUNDEEP
sqlsql-serverview
Martin Dimitrov
Martin Dimitrov

Reputation: 359

Creating a view. Replacing a NULL value with an empty string. SQL

I want to create a view from a table where we have a FirstName, LastName and MiddleName column and where the MiddleName might be NULL, plus a salary. I need to replace the MiddleName with and empty string where there's not MiddleName. I'm using SQL Management Studio. I came up with this code:

CREATE VIEW V_EmployeeNameJobTitle AS
SELECT FirstName  + ' ' + MiddleName + ' ' + LastName AS [Fullname], Salary FROM Employees WHERE MiddleName IS NOT NULL
UNION SELECT FirstName  + ' ' + LastName AS [Fullname], Salary FROM Employees WHERE MiddleName IS NULL

But this doesn't seem to work as I wanted to, and it's not very pretty. Any other suggestions how I might shorten this. Thanks in advance.

Upvotes: 0

Views: 3118

Answers (3)

juergen d
juergen d

Reputation: 204756

SELECT FirstName + ' ' + 
       (case when MiddleName is null then '' else MiddleName + ' ' end) +
       LastName AS [Fullname]

Upvotes: 2

Zeina
Zeina

Reputation: 1603

Use the ISNULL() function, where it replaces the NULL value int the value you choose. for ex:

CREATE VIEW V_EmployeeNameJobTitle AS
SELECT FirstName  + ' ' + ISNULL(MiddleName, '') + ' ' + LastName AS [Fullname], Salary 
FROM Employees

or if you don't want to get 2 spaces when the middle name is null, you can undergo the following:

CREATE VIEW V_EmployeeNameJobTitle AS
SELECT (CASE WHEN MiddleName IS NULL THEN FirstName  + ' ' + LastName
             ELSE FirstName  + ' ' + MiddleName + ' ' + LastName END) AS [Fullname], 
Salary 
FROM Employees

Upvotes: 3

Tedo G.
Tedo G.

Reputation: 1565

use IsNull function 

 CREATE VIEW V_EmployeeNameJobTitle AS
    SELECT FirstName  + ' ' 
         + MiddleName + ' ' 
         + IsNull(LastName, '') AS [Fullname]
   , Salary 
 FROM Employees

Upvotes: 1

Related Questions