SQL Puzzle | The Tricky NULL Puzzle – 1

In this puzzle you have to replace the NULL with singlequotes(”) without using ISNULL and coalesce functions. You also cannot use CASE Condtion. For more details please check the sample input and expected output.

Sample Input

Expected Output

Script

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

--



CREATE TABLE HandleNulls1
(
   Id VARCHAR(10)  
)
GO

INSERT INTO HandleNulls1 VALUES
('a'),
(NULL),
('c')


--

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,NULL) Id FROM HandleNulls1


--

Output-1

--

 
/*------------------------
SELECT CONCAT(Id,NULL) Id FROM HandleNulls1
------------------------*/
Id
----------
a

c

(3 row(s) affected)


--

Solution – 2 | Another one with IIF

--

SELECT IIF(Id IS NULL,'',Id) FROM HandleNulls1


--

Output-2

--

 
/*------------------------
SELECT IIF(Id IS NULL,'',Id) FROM HandleNulls1
------------------------*/

----------
a

c

(3 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