Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Multiple Ways to Split a String Puzzle, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SELECT Puzzle, Split a string in sql, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL SERVER Puzzles, SQL Skills, SQL Split a string, SQL Sudoku, SQLSERVER, string split, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries, TSQL Query to split a string, ways to split a string, XMLPath to split a string
T-SQL Query | [ Multiple Ways to Split a String Puzzle ]
Puzzle Statement
- The puzzle is very simple. We have an input table TestMultipleColumns with two columns ID, Name.
- We have to split the column via comma – Name into two columns named Name and Surname.
- E.g. Pawan,Kumar would be Pawan in the name column and Kumar in the Surname column
- 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
Improving my SQL BI Skills 
Pingback: SQL Server String Puzzles & Tricks | Improving my SQL BI Skills
SELECT Id, LEFT(c.Name,CHARINDEX(‘,’,c.Name)-1) AS Name , SUBSTRING(c.Name,CHARINDEX(‘,’,c.Name)+1,1000) AS Surname FROM [dbo].[TestMultipleColumns] AS c;
— in that case consider the max length of the string is 1000
LikeLike
SELECT Id
,LEFT(Name,CHARINDEX(‘,’,Name)-1)
,RIGHT(Name,Len(Name)-CHARINDEX(‘,’,Name))
FROM [dbo].[TestMultipleColumns]
LikeLike
select id, SUBSTRING(name,1,charindex(‘,’,name)-1) as Name, reverse(substring(reverse(name),1,charindex(‘,’,reverse(Name))-1)) as Surname from [dbo].[TestMultipleColumns]
LikeLike