Click here to Skip to main content
14,691,233 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
One of my field contain a value "USA;;Japan;;India"
I was trying to split it by ";;"
so that I should get the result

USA
Japan
India

I can obtain the first string(ie.USA) by using the following query.
select SUBSTRING(Field1, 0, PATINDEX('%;;%',Field1)) as location from Table1.


How can I get the other two string.
Thanks in advance.
Posted

check this link

http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx[^]

The link uses ',' as a separater. For your case you can change ',' to ';'
   
declare @fld varchar(50);
declare @str1 varchar(50);
declare @x int;
declare @str2 varchar(50);
declare @str3 varchar(50);
declare @tempstr varchar(50);
Select @fld = Field1 from table1;
Set @x=PATINDEX('%;;%',@fld);
Set @str1=SUBSTRING(Field1,0,@x);
Set @tempstr=substring(@fld,@x+2,999);
Set @x=PATINDEX('%;;%',@tempstr);
Set @str2=SUBSTRING(@tempstr,0,@x);
Set @str3=substring(@tempstr,@x+2,999);
select @str1 as location, @str2 as cntry1, @str3 as cntry2
   
try this simple solution
DECLARE @S varchar(max),
  @Split char(1),
  @X xml
SELECT @S = 'USA;Japan;India',
@Split = ';'
SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')
SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c)
   
v2
Another way is to use CTE[^]:
DECLARE @string VARCHAR(30)

SET @string= 'USA;;Japan;;India'


;WITH Splitter AS
(
    SELECT LEFT(@string, CHARINDEX(';;',@string)-1) AS MyWord, RIGHT(@string, LEN(@string) - CHARINDEX(';;',@string)-1) AS Remainder
    WHERE CHARINDEX(';;',@string)>0
    UNION ALL
    SELECT LEFT(Remainder, CHARINDEX(';;',Remainder)-1) AS MyWord, RIGHT(Remainder, LEN(Remainder)-CHARINDEX(';;',Remainder)-1) AS Remainder
    FROM Splitter
    WHERE CHARINDEX(';;',Remainder)>0
    UNION ALL
    SELECT Remainder AS MyWord, NULL AS Remainder
    FROM Splitter
    WHERE CHARINDEX(';;',Remainder)=0
)
SELECT *
FROM Splitter


Result:
USA     Japan;;India
Japan   India
India   NULL
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900