Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

1 https://msbiskills.com/2017/02/15/sql-puzzle-the-isnull-puzzle/
2 https://msbiskills.com/2016/07/26/sql-puzzle-remove-nulls-via-row-wise-pattern/
3 https://msbiskills.com/2016/05/18/sql-puzzle-the-null-columns-puzzle/
4 https://msbiskills.com/2016/02/07/sql-puzzle-the-remove-null-puzzle/
5 https://msbiskills.com/2015/08/06/t-sql-query-the-remove-nulls-puzzle/
6 https://pawankkmr.wordpress.com/2015/05/10/t-sql-query-the-previous-valuenon-null-puzzle/
7 https://pawankkmr.wordpress.com/2015/04/02/t-sql-query-count-null-values-puzzle/
8 https://pawankkmr.wordpress.com/2015/03/31/t-sql-query-group-by-remove-null-uom-puzzle/
9 https://pawankkmr.wordpress.com/2012/07/24/t-sql-6/
10 https://msbiskills.com/2017/12/26/sql-puzzle-the-tricky-null-puzzle-1/

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

Advertisements