concat with different column with different conditions-CodePudding

--Create Table
CREATE TABLE PhoneNumbers
(
    Id INT,
    Name VARCHAR(10),
    MobileNum VARCHAR(100),
    ResidenceNum VARCHAR(100)
)

--Insert Data
INSERT INTO PhoneNumbers VALUES
(1,'Joey','9762575422',NULL),
(2,'Ross', '9987796244','8762575402'),
(3,'Chandler',NULL,'7645764689'),
(4,'Monica','8902567839','7825367901'),
(5,'Rachel',NULL,'7845637289'),
(6,'Pheobe','9872435789','9838653469'),
(7,'Gunther',NULL,NULL),
(8,'Mike',NULL,'9700103678')

--Verify Data
Select * from PhoneNumbers

I want to Generate a new column called Nums (Number to call). The conditions to generate the new column (Nums) are:

If the MobileNum is not null then take the value from the MobileNum and append ‘MobileNum’ to the data.
If the MobileNum is null then take the value from the ResidenceNum and append ‘ResidenceNum’ to the data.
If both MobileNum and ResidenceNum are NOT NULL then take the value from the MobileNum and append ‘MobileNum’ to the data.
If both EmpMobileNumber and ResidenceNum are NULL then we should get NULL as output.

CodePudding user response：

This is a simple COALESCE use case and there will be no better option.

SELECT COALESCE(MobileNum, ResidenceNum) FROM PhoneNumbers;

If you really want to prevent this and create a more complex query, you could write a CASE WHEN construct.

SELECT 
CASE WHEN MobileNum IS NULL 
THEN ResidenceNum
ELSE MobileNum END
FROM PhoneNumbers

I guess the point why you are asking this is that you didn't realize that NULL is the default value when a condition is not met. Your question makes the impression you need to add further cases to handle this. This is not required. Whenever both values are NULL, the result set of these queries will show them as NULL, too.

CodePudding user response：

You should look into the CASE-expression

CASE
WHEN <something> THEN <something>
WHEN <something_else> THEN <something_else>
ELSE <if_nothing_fits>
END

Think about the correct order of the sections because they are being resolved from top to bottom. If something further up is true, then the other sections are not beeing resolved.