Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hi,
 
Im having a table with Places Name seperated by a delimiter.
I want to split the Places name Seperately based on delimiter.
How to achieve this using a query?
 
Example:
 
create table #place(Places varchar(100))
insert into #place values('Chennai, Banglore, Mumbai, Calcutta')
 
select Places,Substring(Places,0,charindex(',',Places))Place1
,Substring(Places,charindex(',',Places)+2,LEN(Places)-charindex(',',Places))Place2
from #place
 
For my query output is
 
Places	                              Place1	Place2
Chennai, Banglore, Mumbai, Calcutta   Chennai	Banglore, Mumbai, Calcutta
 
My Required output is
 
Places	                             Place1	Place2	 Place3	Place4
Chennai, Banglore, Mumbai, Calcutta  Chennai	Banglore Mumbai	Calcutta
Posted 27-Mar-13 1:14am
Edited 27-Mar-13 1:24am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

SELECT Places,"place1"=SUBSTRING(places,1,7),"place2"=SUBSTRING(places,9,8)
,"place3"=SUBSTRING(places,19,6),"place4"=SUBSTRING(Places,27,6)  FROM 
 
Output:
 
Place                                   place1   place2          place3   place4
Chennai, Banglore, Mumbai, Calcutta	Chennai	 Banglore	 Mumbai	 Calcutta
  Permalink  
v2
Comments
kirthiga S at 27-Mar-13 7:11am
   
Hi Karruksen,
Here positions are given manually. For single row its ok.. For multiple rows with varying position of delimiter i need a query.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Based on this solution: Efficient way to string split using cte[^], i've made
 

DECLARE @place TABLE(ID INT IDENTITY(1,1), Places varchar(100))
insert into @place (Places) values('Chennai, Banglore, Mumbai, Calcutta')
insert into @place (Places) values('Warsow, Moskow, Berlin, Prague')
insert into @place (Places) values('City1, City2, City3, City4')
 
;WITH SplitSting AS
(
    SELECT
        ID,LEFT(Places,CHARINDEX(',',Places)-1) AS Part
            ,RIGHT(Places,LEN(Places)-CHARINDEX(',',Places)) AS Remainder
        FROM @place
        WHERE Places IS NOT NULL AND CHARINDEX(',',Places)>0
    UNION ALL
    SELECT
        ID,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
            ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
    UNION ALL
    SELECT
        ID,Remainder,null
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
)
SELECT * FROM SplitSting ORDER BY ID
 
Result:
IDPartRemainder
1ChennaiBanglore, Mumbai, Calcutta
1BangloreMumbai, Calcutta
1MumbaiCalcutta
1CalcuttaNULL
2MoskowBerlin, Prague
2BerlinPrague
2PragueNULL
2WarsowMoskow, Berlin, Prague
3City1City2, City3, City4
3City2City3, City4
3City3City4
3City4NULL
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 341
1 Marcin Kozub 225
2 Sergey Alexandrovich Kryukov 220
3 Praneet Nadkar 197
4 Shweta N Mishra 161
0 OriginalGriff 8,149
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 27 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100