SQL Puzzle | The Tricky NULL Puzzle – 2(CONCAT)

In this puzzle you have to concatenate the columns Id, Nr and Code with ‘-‘ but only if the columns Value is NOT NULL. For more details please check the sample input and expected output.

Sample Input

Id Nr Code
a a a
a a NULL
a NULL NULL
a a b
NULL NULL b
NULL a b
NULL NULL NULL

Expected Output

Id Nr Code Joined
a a a a-a-a
a a NULL a-a
a NULL NULL a
a a b a-a-b
NULL NULL b b
NULL a b a-b
NULL NULL NULL

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE HandleNulls ( Id VARCHAR(10) ,Nr VARCHAR(10) ,Code VARCHAR(10) ) GO INSERT INTO HandleNulls VALUES ('a','a','a' ), ('a','a' ,NULL ), ('a',NULL,NULL ), ('a','a' ,'b' ), (NULL,NULL,'b' ), (NULL,'a','b' ), (NULL,NULL,NULL) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- SELECT *,CONCAT ( Id, CASE WHEN Id IS NOT NULL AND Nr IS NOT NULL THEN CONCAT('-',Nr) ELSE Nr END , CASE WHEN Nr IS NOT NULL AND Code IS NOT NULL THEN CONCAT('-',Code) ELSE Code END )Joined FROM HandleNulls -- ```

Output-1

 ```-- /*------------------------ SELECT *,CONCAT ( Id, CASE WHEN Id IS NOT NULL AND Nr IS NOT NULL THEN CONCAT('-',Nr) ELSE Nr END , CASE WHEN Nr IS NOT NULL AND Code IS NOT NULL THEN CONCAT('-',Code) ELSE Code END )Joined FROM HandleNulls ------------------------*/ Id Nr Code Joined ---------- ---------- ---------- -------------------------------- a a a a-a-a a a NULL a-a a NULL NULL a a a b a-a-b NULL NULL b b NULL a b a-b NULL NULL NULL (7 row(s) affected) -- ```

Other Reference Puzzles – The NULL Puzzles

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com