Tags

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


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

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

Expected output

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

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.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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:
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

Advertisements