CODEWITHSUNDEEP
sqlt-sql
JB7
JB7

Reputation: 13

SQL: Combine First Name and Last Name columns but if Null change to 'Name Not Provided'

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'?

Upvotes: 1

Views: 5808

Answers (3)

user2315314
user2315314

Reputation: 1

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

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460148

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

Upvotes: 3

Mark
Mark

Reputation: 1554

SET CONCAT_NULL_YIELDS_NULL ON

SELECT ISNULL(LastName + ', ' + FirstName, 'Name Not Provided') AS Customer

Upvotes: 0

Related Questions