Click here to Skip to main content
14,668,658 members
Rate this:
Please Sign up or sign in to vote.
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

Rate this:
Please Sign up or sign in to vote.

Solution 1

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 ';'
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

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
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

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
Rate this:
Please Sign up or sign in to vote.

Solution 4

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, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100