1

I'm currently building a simple view and need to combine both the First Name and Last Name columns to create a new Customer column. If a First Name and Last Name are not provided I'd like to change this new combined value to 'Name Not Provided'.

Currently I use a simple select statement: LastName + ', ' + FirstName AS Customer
which appears to work fine for combing the data but if the data doesn't exist, it will just return ', '. How do I go about changing this so it returns 'Name Not Provided'?

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
JB7
  • 13
  • 1
  • 3
  • duplicate of http://stackoverflow.com/questions/2916791/sql-server-string-concatenation-with-null – Mark Aug 11 '15 at 11:07
  • Possible Duplicate of http://stackoverflow.com/questions/2916791/sql-server-string-concatenation-with-null – Oracle Nerd Aug 11 '15 at 11:08

3 Answers3

3
SELECT Customer = CASE WHEN FirstName IS NULL AND LastName IS NULL
                       THEN 'Name Not Provided'
                       WHEN FirstName IS NULL AND LastName IS NOT NULL
                       THEN LastName
                       WHEN FirstName IS NOT NULL AND LastName IS NULL
                       THEN FirstName
                  ELSE LastName + ', ' + FirstName END
FROM dbo.TableName

Demo

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • depending on your database settings your solution may return null if firstname is null and last name is not null or vise-versa – Mark Aug 11 '15 at 11:10
0
SET CONCAT_NULL_YIELDS_NULL ON

SELECT ISNULL(LastName + ', ' + FirstName, 'Name Not Provided') AS Customer
Mark
  • 1,544
  • 1
  • 14
  • 26
0

Microsoft's ISNULL() function is used to specify how we want to treat NULL values. The following query will return a default text if FirstName is NULL.

SELECT (ISNULL(FirstName,'First name is null')) AS Customer
Prashant Pimpale
  • 10,349
  • 9
  • 44
  • 84
user2315314
  • 1
  • 1