--查询处理
select clientid
,moneys=stuff(re,1,charindex(':',re),'')
,re=left(re,charindex(':',re)-1)
from(
select clientid
,re=substring(re,id,charindex(';',re+';',id)-id)
from tb a join(
select id=a.id+b.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
)b on substring(';'+a.re,b.id,1)=';'
)a
select clientid
,moneys=stuff(re,1,charindex(':',re),'')
,re=left(re,charindex(':',re)-1)
from(
select clientid
,re=substring(re,id,charindex(';',re+';',id)-id)
from tb a join(
select id=a.id+b.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
)b on substring(';'+a.re,b.id,1)=';'
)a
create table tb(clientid varchar(10),moneys float,re varchar(100))
insert tb select '017',809903.4100,'3月:100000;4月:609903.41;5月:100000'
go--查询处理
select clientid
,moneys=stuff(re,1,charindex(':',re),'')
,re=left(re,charindex(':',re)-1)
from(
select clientid
,re=substring(re,id,charindex(';',re+';',id)-id)
from tb a join(
select id=a.id+b.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
)b on substring(';'+a.re,b.id,1)=';'
)a
go--删除测试
drop table tb/*--测试结果clientid moneys re
---------- ---------------- ----------
017 100000 3月
017 609903.41 4月
017 100000 5月(所影响的行数为 3 行)--*/
select clientid
,moneys=stuff(re,1,charindex(':',re),'')
,re
from(
select clientid
,re=substring(re,id,charindex(';',re+';',id)-id)
from tb a join(
select id=a.id+b.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
)b on substring(';'+a.re,b.id,1)=';'
)a
create table tb(clientid varchar(10),moneys float,re varchar(100))
insert tb select '017',809903.4100,'3月:100000;4月:609903.41;5月:100000'
go--查询处理
select clientid
,moneys=stuff(re,1,charindex(':',re),'')
,re
from(
select clientid
,re=substring(re,id,charindex(';',re+';',id)-id)
from tb a join(
select id=a.id+b.id+1
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b
)b on substring(';'+a.re,b.id,1)=';'
)a
go--删除测试
drop table tb/*--测试结果clientid moneys re
---------- ------------------ -----------------
017 100000 3月:100000
017 609903.41 4月:609903.41
017 100000 5月:100000(所影响的行数为 3 行)
--*/
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
服务器: 消息 11,级别 16,状态 1,行 0
一般性网络错误。请检查网络文档。连接中断
select top 8000 id=identity(int,1,1) into #t from sysobjects,syscolumns
alter table #t add constraint PK_#t primary key(id)
--查询处理
select clientid
,moneys=stuff(re,1,charindex(':',re),'')
,re
from(
select clientid
,re=substring(re,id,charindex(';',re+';',id)-id)
from tb a
join #t b on substring(';'+a.re,b.id,1)=';'
)a--删除临时表
drop table #t