Tags

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


SQL Puzzle | Split String by .(Dot) using XML

In this puzzle you will have to split a string based on dot(.). For more details please check the sample input and expected output.

Sample Input

Id Vals
1 P.K
2 a.b
3 c.d
4 e.J
5 t.u.b

Expected Output

Id Vals Value
1 P.K P
1 P.K K
2 a.b a
2 a.b b
3 c.d c
3 c.d d
4 e.J e
4 e.J J
5 t.u.b t
5 t.u.b u
5 t.u.b b

Script

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

--

CREATE TABLE Splitter
(
	 Id INT
	,Vals VARCHAR(100)
)
GO

INSERT INTO Splitter VALUES
(1,'P.K'),
(2,'a.b'),
(3,'c.d'),
(4,'e.J'),
(5,'t.u.b')
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

--

SELECT Id,Vals,Value FROM 
(
	SELECT *,CAST('<A>'+ REPLACE(Vals,'.','</A><A>')+ '</A>' AS XML) po
	FROM Splitter
)rt
CROSS APPLY ( SELECT t.value('.', 'VARCHAR(10)') Value FROM po.nodes('/A') AS x(t) ) o


--

Output-1

--                               
  
Id          Vals              Value
----------- ----------------- ----------
1           P.K               P
1           P.K               K
2           a.b               a
2           a.b               b
3           c.d               c
3           c.d               d
4           e.J               e
4           e.J               J
5           t.u.b             t
5           t.u.b             u
5           t.u.b             b

(11 rows affected)

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements