SQL Puzzle | The ZERO Blank Puzzle | SQL Interview Question

In this puzzle you have to find difference between a and b column between each row and if the difference is not equal to 0 then show the difference i.e. a – b otherwise 0. Now you need to replace this zero with blank

Please check the sample input and the expected output.

Sample Input

Id a b
1 10 4
2 10 10
3 1 10000000
4 15 15

Expected Output

Id a b OUTPUT
1 10 4 6
2 10 10
3 1 10000000 -9999999
4 15 15

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

 ```-- CREATE TABLE TheZeroPuzzle ( Id INT ,a INT ,b INT ) GO INSERT INTO TheZeroPuzzle VALUES (1,10,4) , (2,10,10) , (3,1, 10000000 ) , (4,15,15) GO SELECT * FROM TheZeroPuzzle 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 | Using CAST Only

 ```-- SELECT * , CASE WHEN a - b <> 0 THEN CAST(a - b AS VARCHAR) ELSE '' END OUTPUT FROM TheZeroPuzzle -- ```

Output-1

 ```-- Id a b OUTPUT ----------- ----------- ----------- -------------------- 1 10 4 6 2 10 10 3 1 10000000 -9999999 4 15 15 (4 rows affected) -- ```

SOLUTION – 2 | Using Case and CONCAT

 ```-- SELECT * , CASE WHEN a - b <> 0 THEN CONCAT(a-b,'') ELSE '' END OUTPUT FROM TheZeroPuzzle -- ```

Output-2

 ```-- Id a b OUTPUT ----------- ----------- ----------- -------------------- 1 10 4 6 2 10 10 3 1 10000000 -9999999 4 15 15 (4 rows affected) -- ```

SOLUTION – 3 | Using STR and CASE

 ```-- SELECT * , CASE WHEN a - b <> 0 THEN STR(a-b) ELSE '' END OUTPUT FROM TheZeroPuzzle -- ```

Output-3

 ```-- Id a b OUTPUT ----------- ----------- ----------- -------------------- 1 10 4 6 2 10 10 3 1 10000000 -9999999 4 15 15 (4 rows affected) -- ```

SOLUTION – 4 | Using CONCAT and CASE

 ```-- SELECT * , CONCAT( CASE WHEN a - b <> 0 THEN a - b END ,'') OUTPUT FROM TheZeroPuzzle -- ```

Output-4

 ```-- Id a b OUTPUT ----------- ----------- ----------- -------------------- 1 10 4 6 2 10 10 3 1 10000000 -9999999 4 15 15 (4 rows affected) -- ```

Please add a comment if you have any other or better solution in mind. I would love to learn it. We all need to learn. Thanks in advance.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook: