How to separate string value and create temp to store a single row in temp table

Question

I am using SQL Server 2005.

I created a table with columns ID and Courses . The entries are as follows:

ID Courses
1. Java, ASP.Net, C #
2. Java
3. Java, C #
4. HTML

The Courses column is of type varchar, and the values ​​in it are separated by a comma. I want to split each word and keep it at a different or pace. table.

The output should be like this:

ID Courses
1. Java
2. ASP.Net
3. C #
4. HTML

Here the identifier is not important.

Thanks.

Answer 1

see my previous answer to this question

this is the best source:

http://www.sommarskog.se/arrays-in-sql.html

create a split function and use it like:

 SELECT * FROM YourTable y INNER JOIN dbo.splitFunction(@Parameter) s ON y.ID=s.Value 

I prefer a numerical table approach

For this method to work, you need to complete this setup at once:

 SELECT TOP 10000 IDENTITY(int,1,1) AS Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 ALTER TABLE Numbers ADD CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number) 

Once the Numbers table is configured, create this function:

 CREATE FUNCTION [dbo].[FN_ListToTable] ( @SplitOn char(1) --REQUIRED, the character to split the @List string on ,@List varchar(8000)--REQUIRED, the list to split apart ) RETURNS TABLE AS RETURN ( ---------------- --SINGLE QUERY-- --this will not return empty rows ---------------- SELECT ListValue FROM (SELECT LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue FROM ( SELECT @SplitOn + @List + @SplitOn AS List2 ) AS dt INNER JOIN Numbers n ON n.Number < LEN(dt.List2) WHERE SUBSTRING(List2, number, 1) = @SplitOn ) dt2 WHERE ListValue IS NOT NULL AND ListValue!='' ); GO 

Now you can easily split the CSV row into a table and join it:

 select * from dbo.FN_ListToTable(',','1,2,3,,,4,5,6777,,,') 

CONCLUSION:

 ListValue ----------------------- 1 2 3 4 5 6777 (6 row(s) affected) 

To do what you need, use CROSS APPLY:

 DECLARE @tbl_A table (RowID int, RowValue varchar(500)) DECLARE @tbl_b table (RowID int identity, RowValue varchar(500)) INSERT INTO @tbl_A VALUES (1, 'Java, ASP.Net, C#') INSERT INTO @tbl_A VALUES (2, 'Java') INSERT INTO @tbl_A VALUES (3, 'Java, C#') INSERT INTO @tbl_A VALUES (4, 'html') INSERT INTO @tbl_b (RowValue) SELECT DISTINCT st.ListValue FROM @tbl_A CROSS APPLY dbo.FN_ListToTable(',',RowValue) AS st ORDER BY st.ListValue SELECT * FROM @tbl_b ORDER BY RowID 

CONCLUSION:

 RowID RowValue ----------- -------------- 1 ASP.Net 2 C# 3 html 4 Java (4 row(s) affected)