SQL - Split string

In this post i will demonstrate how can you split a string by using SQL
If you have a string like:
1,James,USA;2,Amy,Canada;3,Charles,Singapore;
and want to split this to the format below:

UserID	UserName	Country
1	James	USA
2	Amy	Canada
3	Charles	Singapore

basically you need to loop thru the string to search for delimeter i.e. semicolon to get one user record, and splitting the sub string again using another delimeter i.e. comma to get the user's UserID, UserName, Country

NOTE: in whatever splitting function, you need to make sure to format variable is properly formatted else more customer checking needed

DECLARE @string varchar(100) SET @string = '1,James,USA;2,Amy,Canada;3,Charles,Singapore;' DECLARE @delimiter1 CHAR(1) DECLARE @delimiter2 CHAR(1) SET @delimiter1 = ';' SET @delimiter2 = ',' DECLARE @pos as int SET @pos = charindex(@delimiter1, @string) DECLARE @temp AS NVARCHAR(1000) DECLARE @country NVARCHAR(100) DECLARE @username NVARCHAR(100) DECLARE @userid INT DECLARE @tbl_user TABLE (userid int, username varchar(50), country varchar(50)) WHILE(@pos > 0) BEGIN SET @temp = SUBSTRING(@string, 0, @pos) SET @userid = SUBSTRING(@temp, 0, CHARINDEX(@delimiter2, @temp)) SET @username = SUBSTRING(@temp, CHARINDEX(@delimiter2, @temp) + 1, CHARINDEX(@delimiter2, @temp, CHARINDEX(@delimiter2, @temp) + 1) - CHARINDEX(@delimiter2, @temp) - 1) SET @country = SUBSTRING(@temp, CHARINDEX(@delimiter2, @temp, CHARINDEX(@delimiter2, @temp) + 1) + 1, @pos - CHARINDEX(@delimiter2, @temp, CHARINDEX(@delimiter2, @temp) + 1)) INSERT INTO @tbl_user (userid, username, country) VALUES (@userid, @username, @country) SET @string = SUBSTRING(@string, charindex(@Delimiter1,@String) + 1, LEN(@string) - charindex(@Delimiter1,@String)) SET @pos = charindex(@Delimiter1,@string) END SELECT * FROM @tbl_user

note
As you can see, the above sql utilize more on using SUBSTRING and also CHARINDEX function to get the position of each delimeter and extract the data based on the specific location index

SET @string = '1,James,USA;2,Amy,Canada;3,Charles,Singapore;'
SET @temp = SUBSTRING(@string, 0, @pos)
Result: 1,James,USA - to extract the first set of user record

SET @userid = SUBSTRING(@temp, 0, CHARINDEX(@delimiter2, @temp))
Result: 1 - to get the first userid which will start from the index 0 and to length of the first character index of comma

gettting the @username and @country also utilizing this approach

SET @string = SUBSTRING(@string, charindex(@Delimiter1,@String) + 1, LEN(@string) - charindex(@Delimiter1,@String))
- to get the remaining set of user records i.e 2,Amy,Canada;3,Charles,Singapore;

SET @pos = charindex(@Delimiter1,@string)
- to get the position of next semicolon