SQL Puzzle | Different ways to remove the last comma from a string | SQL INTERVIEW QUESTION

In this puzzle you have to remove the last comma from a string if present. If the last character is not comma then just get the data as it is.

Please check the sample input and the expected output.

Sample Input

Id Val
1 Pawan
2 Pawan,Avtaar,
3 ,
4 Hello,
5 a,a,b,c,
6 NULL
7

Expected Output

Id Val
1 Pawan
2 Pawan,Avtaar
3
4 Hello
5 a,a,b,c
6 NULL
7

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

 ```-- CREATE TABLE RemoveLastComma ( Id INT ,Val VARCHAR(100) ) GO INSERT INTO RemoveLastComma VALUES (1,'Pawan'), (2,'Pawan,Avtaar,'), (3,','), (4,'Hello,'), (5,'a,a,b,c,'), (6,NULL), (7,'') GO SELECT * FROM RemoveLastComma GO -- ```

Rules/Restrictions

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

SOLUTION – 1

 ```-- SELECT Id, IIF(RIGHT(Val,1) = ',' , SUBSTRING(Val,0,LEN(Val)) , Val) Val FROM RemoveLastComma -- ```

Output-1

 ```-- Id Val ----------- ---------------- 1 Pawan 2 Pawan,Avtaar 3 4 Hello 5 a,a,b,c 6 NULL 7 (7 rows affected) -- ```

SOLUTION – 2

 ```-- SELECT Id, IIF(RIGHT(Val,1) = ',' , SUBSTRING(Val,1,LEN(Val)-1) , Val) Val FROM RemoveLastComma -- ```

Output-2

 ```-- Id Val ----------- ---------------- 1 Pawan 2 Pawan,Avtaar 3 4 Hello 5 a,a,b,c 6 NULL 7 (7 rows affected) -- ```

SOLUTION – 3

 ```-- SELECT Id, IIF(RIGHT(Val,1) = ',' , LEFT(Val,LEN(Val)-1) , Val) Val FROM RemoveLastComma -- ```

Output-3

 ```-- Id Val ----------- ---------------- 1 Pawan 2 Pawan,Avtaar 3 4 Hello 5 a,a,b,c 6 NULL 7 (7 rows affected) -- ```

SOLUTION – 4

 ```-- SELECT Id, IIF(RIGHT(Val,1) = ',' , STUFF(Val,LEN(Val),1,'') , Val) Val FROM RemoveLastComma -- ```

Output-4

 ```-- Id Val ----------- ---------------- 1 Pawan 2 Pawan,Avtaar 3 4 Hello 5 a,a,b,c 6 NULL 7 (7 rows affected) -- ```

SOLUTION – 5

 ```-- SELECT Id, IIF(RIGHT(Val,1) = ',' , REVERSE(SUBSTRING(REVERSE(Val),2,LEN((Val)))) , Val) Val FROM RemoveLastComma -- ```

Output-5

 ```-- Id Val ----------- ---------------- 1 Pawan 2 Pawan,Avtaar 3 4 Hello 5 a,a,b,c 6 NULL 7 (7 rows affected) -- ```

Enjoy 🙂

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

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/