Click here to Skip to main content
11,496,146 members (677 online)
The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.
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
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
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.

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

  Print Answers RSS
0 RyanDev 230
1 Sergey Alexandrovich Kryukov 114
2 Richard Deeming 100
3 OriginalGriff 89
4 Mostafa Asaduzzaman 59
0 Sergey Alexandrovich Kryukov 10,401
1 OriginalGriff 8,910
2 Sascha Lefèvre 3,899
3 Maciej Los 3,422
4 Richard Deeming 2,600


Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 27 Mar 2013
Copyright © CodeProject, 1999-2015
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