在SQL Server中如何Text字段如何分离存放？
已经一个表A如下
其中Note字段为Text数据类型，Note中的分隔符是以回车换行
id Note
1 5.txt 6.txt 7.txt
2 8.txt 9.txt 10.txt
对表A进行如何的处理成为B表如下：
id FileName
1 5.txt
2 6.txt
3 7.txt
4 8.txt
5 9.txt
6 10.txt
------------------------------
[解决办法]
Create Table A
(id Int,
Note Text)
Insert A Select 1, '5.txt 6.txt 7.txt '
Union All Select 2, '8.txt 9.txt 10.txt '
GO
Select Top 100 ID = Identity(Int, 1, 1) Into #T From Syscolumns A, Syscolumns B
Select
ID = Identity(Int, 1, 1),
FileName = Substring(A.Note, B.ID, CharIndex( ' ', Cast(A.Note As Varchar) + ' ', B.ID) - B.ID)
Into B
From A, #T B
Where CharIndex( ' ', Cast(A.Note As Varchar) + ' ', B.ID) > 0
And Substring( ' ' + Cast(A.Note As Varchar), B.id, 2) = ' '
Order By A.ID, FileName
Select * From B Order By ID
GO
Drop Table #T, B
Drop Table A
--Result
/*
idFileName
15.txt
26.txt
37.txt
48.txt
59.txt
610.txt
*/
[解决办法]
学习，没见过这种写法，最好解释一下，谢谢
