【SQL】字符串系列2：将一个字符串根据特定字符分拆成多行

      
      

       
       Select * From dbo.StrToColEx('数学,物理,历史,生物,语文,英语,化学,地理',',') b
      
      
      
      

       
       
1.
      
      

      
      

       
       CREATE function dbo.StrToColEx (@str Varchar(max),@separator varchar(10))  
       
       
returns @T table(Col varchar(max),sort int)      
       
       
as      
       
       
Begin      
       
       
      
       
       
Declare @i int      
       
       
declare @index int    
       
       
set @index=1      
       
       
Set @i=0      
       
       
Set @[email protected][email protected]            
       
       
While(@i<=len(@str))      
       
       
   Begin      
       
       
       Insert @T select   substring(@str,@i,charindex(@separator,@str COLLATE Chinese_PRC_CS_AS_KS_WS,@i)[email protected]) ,@index    
       
       
       Select @i=charindex(@separator,@str COLLATE Chinese_PRC_CS_AS_KS_WS,@i)+LEN(@separator)  
       
       
       set @[email protected]+1  
       
       
    End      
       
       
return      
       
       
End
      
      
      
      

       
       
1.
       
       
2.
       
       
3.
       
       
4.
       
       
5.
       
       
6.
       
       
7.
       
       
8.
       
       
9.
       
       
10.
       
       
11.
       
       
12.
       
       
13.
       
       
14.
       
       
15.
       
       
16.
       
       
17.
       
       
18.