传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
各位大神帮忙看下,有些表用这个脚本没有问题,有些表用这个 脚本就会报错。一般是输出20行左右就会固定报 这个错误。
Declare @id int
Set @id=1
while @id<=(select MAX([users_id]) from [CSMBTEST].[dbo].[Users])
begin
Declare @i int, @j int, @c nvarchar(max), @title nvarchar(max), @nt nvarchar(50) ,@allfraction int, @ip nvarchar(20), @examcount int
Set @nt=(select [Users_LoginName] from [CSMBTEST].[dbo].[Users] where [users_id]=@id)
Set @allfraction=(select [Users_AllFraction] from [CSMBTEST].[dbo].[Users] where [users_id]=@id)
Set @ip=(select [Users_Ip] from [CSMBTEST].[dbo].[Users] where [users_id]=@id)
Set @examcount=(select [Users_ExamCount] from [CSMBTEST].[dbo].[Users] where [users_id]=@id)
Set @c=(select replace(replace(replace([Users_Question],' ',''),'</p>','|||'),'</DIV>','|||')
from [CSMBTEST].[dbo].[Users] where [users_id]=@id)
Set @i=CHARINDEX('<',@c,1)
Set @j=CHARINDEX('>',@c,1)
while @i>0
begin
Set @c=LEFT(@c, @i-1) + right(@c, LEN(@C)-@j)
Set @i=CHARINDEX('<',@c,1)
Set @j=CHARINDEX('>',@c,1)
end
Set @title = LEFT(@c, CHARINDEX('|||', @c, 1)-1)
Set @c = Right(@c, len(@c)-CHARINDEX('|||', @c, 1)-2)
--select @c, @title
Declare @T table(Title nvarchar(max), text nvarchar(max))
Declare @h int, @v nvarchar(max)
Set @h=CHARINDEX('||||||',@c,1)
while @h>0
begin
Set @v=LEFT(@c, @h-1)
Set @c=Right(@c, len(@c)-@h-5)
insert into @T values(@title, RIGHT(@v,CHARINDEX(' ',REVERSE(@v)) ))
Set @h=CHARINDEX('||||||',@c,1)
end
INSERT INTO [CSMBTEST].[dbo].[Users_New]
select [Users_LoginName]=@nt
,[Users_AllFraction]=@allfraction
,[Users_Ip]=@ip
,[Users_ExamCount]=@examcount
--,[Question]=LEFT([Text],CHARINDEX('(',[Text] collate Chinese_PRC_CS_AS_WS)-1)
,[Question]=replace(SUBSTRING([Text],CHARINDEX('.',[Text])+1,CHARINDEX('(',[Text] collate Chinese_PRC_CS_AS_WS)- CHARINDEX('.',[Text])-1),'?','?')
,[Selection]=SUBSTRING([Text],CHARINDEX('(',[Text] collate Chinese_PRC_CS_AS_WS),CHARINDEX(')',[Text] collate Chinese_PRC_CS_AS_WS)-CHARINDEX('(',[Text] collate Chinese_PRC_CS_AS_WS)+1)
,[True_Flag]=CASE WHEN SUBSTRING([Text],CHARINDEX(')',[Text] collate Chinese_PRC_CS_AS_WS)+1,2)='正确' THEN 1 ELSE 0 END
,[Answer]=CASE WHEN CHARINDEX('正确答案:',[Text])=0 THEN NULL ELSE SUBSTRING([Text],CHARINDEX('正确答案:',[Text])+5,CHARINDEX('|||',[Text])-CHARINDEX('正确答案:',[Text])-5) END
,[Option]=SUBSTRING([Text],CHARINDEX('|||',[Text]),LEN([Text])-CHARINDEX('|||',[Text]))
From @T
ORDER BY [Users_LoginName] ASC
--每次插入后清空临时表
delete @T
set @id=@id+1
end
好吧,看来要一个个拆分出来看看。。。不知道有哪位大神有比较简单的排除方法