SQL Puzzle | Multiple ways to Split a string and get nth ROW | Advanced SQL

In this puzzle you have to split the string and get me the third value from the list. The challenge is to do that using a single SELECT. For more details please see the sample input and expected output.

Sample Input

Expected Output

Script – DDL and INSERT Sample Data

--

DECLARE @ AS VARCHAR(MAX) = 'Pawan1,Pawan2,Pawan4,Pawan3' 

--

SOLUTION – 1

--

DECLARE @ AS VARCHAR(MAX) = 'Pawan1,Pawan2,Pawan4,Pawan3' 
SELECT VALUE FROM 
(
	SELECT VALUE , ROW_NUMBER() OVER (ORDER BY (SELECT null)) rnk FROM STRING_SPLIT(@, ',')
)x where rnk = 3
GO

--

Output – 1

--

VALUE
--------------
Pawan4

(1 row affected)

--

SOLUTION – 2

--

DECLARE @ AS VARCHAR(MAX) = 'Pawan1,Pawan2,Pawan4,Pawan3' 
SELECT CAST('<p>' + REPLACE(@ , ',','</p><p>') + '</p>' AS XML).value('/p[3]','VARCHAR(10)')
GO

--

Output – 2

--


----------
Pawan4

(1 row 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