存储过程中很复杂的字符串截取，弄了几天，我头大了
有如下字符串：（下列字符间相连，没有空格，为了方便观看，手工弄的空格）
离线记录^songlijun|8888888888|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750
%离线记录^xieyuan|0540002070|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750 $5480011708|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750
$5480011701|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750
$0540002070|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750
%离线记录^xieyuan|0540002070|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750
$0540002070|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750
解释：
上面是多个文件中的数据拼接起来的。
以“%”相隔的是不同文件中的数据，上面只有3个文件，故只有2个“%”，若有n个文件，那么会有(n-1)个“%”。
以“|”相隔的依次为物流员（如“离线记录^songlijun”，物流员为songlijun），站点编号，包编号集合（如“3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750”）
以“^”相隔的“离线记录”为固定内容，“^”后为物流员（songlijun）
以“#”相隔的为包编号(如“3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750”中共有6个包编号，形如“3500320083208”)
目的：
得到物流员，站点编号，包编号（如“3500320083208”），插入到表中。
遇到问题：
做了几天，if-else太多了，弄得几个就晕了。
希望有好心人帮忙！感激不尽啊！第一次发帖，不知道给多少分。分不够的话，还可以再加啊！ 要联系我的话，可以加我QQ：872082002
[解决办法]
/*create function [dbo].[m_split2](@c varchar(max),@split varchar(2)) returns @t table(col varchar(max)) as begin while(charindex(@split,@c)<>0) begin if(substring(@c,1,charindex(@split,@c)-1)!=' ') begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) end set @c = stuff(@c,1,charindex(@split,@c),'') end if(@c!=' ') begin insert @t(col) values (@c) end return end*/declare @T nvarchar(max)set @T='离线记录^songlijun|8888888888|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750%离线记录^xieyuan|0540002070|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750$5480011708|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750$5480011701|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750$0540002070|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750%离线记录^xieyuan|0540002070|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750$0540002070|3500320083208#3500600520890#3500741122924#3500790143280#3501010510869#3500741129750'select * into #t from [dbo].[m_split2](@T,'%')update #t set col=replace(col,' ','')update #t set col=replace(col,char(13),'')update #t set col=replace(col,char(10),'')select substring(col,charindex('^',col)+1,charindex('|',col)-charindex('^',col)-1) as id,right(col,len(col)-charindex('|',col)) as [value] into #t2from #tdrop table #tselect id, left(value,charindex('|',value)-1) as nid,right(value,len(value)-charindex('|',value)+1) as value into #t3 from (select A.id, B.value from( select id, [value] = convert(xml,' <root> <v>' + replace([value], '$', ' </v> <v>') + ' </v> </root>') from #t2 )A outer apply( select value = N.v.value('.', 'varchar(100)') from A.[value].nodes('/root/v') N(v) )B ) aadrop table #t2select A.id as 物流员, A.nid as 站点编号,B.value as 编号集合 from( select id,nid, [value] = convert(xml,' <root> <v>' + replace([value], '#', ' </v> <v>') + ' </v> </root>') from #t3 )A outer apply( select value = N.v.value('.', 'varchar(100)') from A.[value].nodes('/root/v') N(v) )Bdrop table #t3/*物流员 站点编号 编号集合------------- ------------- --------------------songlijun 8888888888 3500320083208 songlijun 8888888888 3500600520890 songlijun 8888888888 3500741122924 songlijun 8888888888 3500790143280 songlijun 8888888888 3501010510869 songlijun 8888888888 3500741129750 xieyuan 0540002070 3500320083208 xieyuan 0540002070 3500600520890 xieyuan 0540002070 3500741122924 xieyuan 0540002070 3500790143280 xieyuan 0540002070 3501010510869 xieyuan 0540002070 3500741129750 xieyuan 5480011708 3500320083208 xieyuan 5480011708 3500600520890 xieyuan 5480011708 3500741122924 xieyuan 5480011708 3500790143280 xieyuan 5480011708 3501010510869 xieyuan 5480011708 3500741129750 xieyuan 5480011701 3500320083208 xieyuan 5480011701 3500600520890 xieyuan 5480011701 3500741122924 xieyuan 5480011701 3500790143280 xieyuan 5480011701 3501010510869 xieyuan 5480011701 3500741129750 xieyuan 0540002070 3500320083208 xieyuan 0540002070 3500600520890 xieyuan 0540002070 3500741122924 xieyuan 0540002070 3500790143280 xieyuan 0540002070 3501010510869 xieyuan 0540002070 3500741129750 xieyuan 0540002070 3500320083208 xieyuan 0540002070 3500600520890 xieyuan 0540002070 3500741122924 xieyuan 0540002070 3500790143280 xieyuan 0540002070 3501010510869 xieyuan 0540002070 3500741129750 xieyuan 0540002070 3500320083208 xieyuan 0540002070 3500600520890 xieyuan 0540002070 3500741122924 xieyuan 0540002070 3500790143280 xieyuan 0540002070 3501010510869 xieyuan 0540002070 3500741129750 (42 row(s) affected)*/