Click here to Skip to main content
14,304,362 members
Rate this:
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
Updated 6-Aug-19 2:29am
v3
Rate this:
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
   
v2
Rate this:
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
   
v2
Comments
kirthiga S 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)




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