Tags

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


T-SQL Query | [ Multiple Ways to Split a String Puzzle ]

Puzzle Statement

  1. The puzzle is very simple. We have an input table TestMultipleColumns with two columns ID, Name.
  2. We have to split the column via comma – Name into two columns named Name and Surname.
  3. E.g. Pawan,Kumar would be Pawan in the name column and Kumar in the Surname column
  4. Please check out the sample input and expected output for details.

Sample Input

Id Name
1 Pawan,Kumar
2 Sandeep,Goyal
3 Isha,Mattoo
4 Gopal,Ranjan
5 Neeraj,Garg
6 Deepak,Sharma
7 Mayank,Tripathi

 Expected output

Id Name Surname
1 Pawan Kumar
2 Sandeep Goyal
3 Isha Mattoo
4 Gopal Ranjan
5 Neeraj Garg
6 Deepak Sharma
7 Mayank Tripathi

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

Script

Use the below script to generate the source table and fill them up with the sample data.

--

CREATE TABLE [dbo].[TestMultipleColumns]

(

[Id] [int] NULL,

[Name] [varchar](20) NULL

)

INSERT INTO [TestMultipleColumns] VALUES

(1,    'Pawan,Kumar'),

(2,    'Sandeep,Goyal'),

(3,    'Isha,Mattoo'),

(4,    'Gopal,Ranjan'),

(5,    'Neeraj,Garg'),

(6,    'Deepak,Sharma'),

(7,    ' Mayank,Tripathi')

GO

--

UPDATE – 11-Apr-2015 – Solution 1,2 & 3


--

--SOLUTION 1 - Using PARSENAME
SELECT Id,
       PARSENAME(REPLACE(Name,',','.'),2) Name,
       PARSENAME(REPLACE(Name,',','.'),1) Surname
FROM TestMultipleColumns

--SOLUTION 2 - USING SUBSTRING & CHARINDEX
SELECT Id,
       SUBSTRING(Name,1,CHARINDEX(',',Name)-1) Name,
       SUBSTRING(Name,CHARINDEX(',',Name)+1,LEN(Name)) Surname
 FROM TestMultipleColumns 

--SOLUTION 3 - Using XML method
;With CTE
AS
(
    SELECT Id, Name,
    CAST(('<r><n>' + REPLACE(Name,',', '</n><n>') + '</n></r>') AS XML) X
   FROM TestMultipleColumns
)
SELECT  Id,
 i.value('n[1]','VARCHAR(50)') AS Name,
 i.value('n[2]','VARCHAR(50)') AS Surname
FROM CTE c
CROSS APPLY c.X.nodes('/r') x(i)

--SOLUTION 4
--For comma seperated string


DECLARE @x AS XML=''
DECLARE @Param AS VARCHAR(100) = 'Ind,Hary,Gu'
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)
SELECT t.value('.', 'VARCHAR(10)') Value FROM @x.nodes('/A') AS x(t)

--

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

Keep Learning

Http://MSBISkills.com

Advertisements