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

SQL - Create table

Creating temporary table
1. using #

CREATE TABLE #tbl_user(userid int,username varchar(50)) INSERT INTO #tbl_user (userid, username) SELECT 1, 'user name 1' UNION ALL SELECT 2, 'user name 2' UNION ALLS SELECT 3, 'user name 3' SELECT * FROM #tbl_user DROP TABLE #tbl_user

note
Here im using the # hash sign when creating the temporary table which tell the SQL Server i want to create a local temporary table located in the tempdb. If you are creating this table within one store procedure, this table will only usable within this store procedure and it will automatically dropped when its out of the store procedure scope

2. using @

DECLARE @tbl_user TABLE ( userid int, username varchar(50)) INSERT INTO @tbl_user (userid, username) SELECT 1, 'user name 1' UNION ALL SELECT 2, 'user name 2' UNION ALL SELECT 3, 'user name 3' SELECT * FROM @tbl_user

note
Here im using the @ sign when creating the temporary table which tell the SQL Server i want to create a local temporary table in the memory. This variable will auto cleared itself once it goes out of it scope

Creating permanent table

CREATE TABLE tbl_user ( userid int, username varchar(50)) INSERT INTO tbl_user (userid, username) SELECT 1, 'user name 1' UNION ALL SELECT 2, 'user name 2' UNION ALL SELECT 3, 'user name 3' SELECT * FROM tbl_user --DROP TABLE tbl_user

note
Here im trying to create a permanent table in the same database where this query runs. This is the most common where you want to store the data permanently

SQL - Select multiple rows into single column

Here im going to demonstrate how can you concate all the values in multiple rows into a single value:

Data
TABLE: @tbl_user_request

Raw data
userid request_id
1 'A000001'
1 'A000002'
2 'A000003'
3 'A000004'
4 'A000005'
5 'A000006'
5 'A000007'

Expected result
userid request_id
1 A000001,A000002
2 A000003
3 A000004
4 A000005
5 A000006,A000007

SQL

DECLARE @tbl_user_request TABLE (userid int, request_id varchar(50)) INSERT INTO @tbl_user_request SELECT 1, 'A000001' UNION ALL SELECT 1, 'A000002' UNION ALL SELECT 2, 'A000003' UNION ALL SELECT 3, 'A000004' UNION ALL SELECT 4, 'A000005' UNION ALL SELECT 5, 'A000006' UNION ALL SELECT 5, 'A000007' SELECT a.userid, request_id=substring((SELECT ( ', ' + request_id) FROM @tbl_user_request b WHERE a.userid=b.userid ORDER BY userid FOR XML PATH('')), 3, 1000)FROM @tbl_user_request a GROUP BY userid

note

There is a subquery in the SQL which joining the same table as the main query. The purpose of this subquery is to select all the "requet_id" pertaining to the same userid in the "tbl_user" table and concatenate them into a single "concatename" variable by using the FOR XML PATH

For more example you can refer here:
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist