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