SQL PUZZLE | Can you remove Single/Multiple leading and trailing Commas ? | SQL Interview Question

In this puzzle you have to remove leading and trailing commas from a string

For more details please refer sample input and expected output.

Sample Input

Expected output

Script – DDL and INSERT sample data

--

CREATE TABLE [RemoveMultipleTrailing&LeadingCommas]
(
	 Id INT
	,Val VARCHAR(100)
)
GO

INSERT INTO [RemoveMultipleTrailing&LeadingCommas] VALUES
(1,',,Pawan'),
(2,',Pawan,,,,,'),
(3,','),
(4,',,Hello,'),
(5,'a,a,b,c,,,,,,,,'),
(6,NULL),
(7,'')
GO

SELECT * FROM [RemoveMultipleTrailing&LeadingCommas]
GO

--

SOLUTION 1 | Using PATINDEX

--

SELECT * , 	
STUFF(LEFT(Val, LEN(Val) - PATINDEX('%[^,]%', REVERSE(Val)) + 1), 1, PATINDEX('%[^,]%', Val) - 1, '') newVal
FROM [RemoveMultipleTrailing&LeadingCommas]

--

OUTPUT 1

--

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

(7 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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.

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:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com