USE [METRIX]
GO
/****** Object: StoredProcedure [dbo].[SplitString] Script Date: 11/12/ 12:07:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SplitString]
as
declare @strSplitString nvarchar(1000)--要拆分的字符串
declare @strSplit nvarchar(10)-- 分隔符,如:逗号
set @strSplit=';'
declare @location int
declare @strat int
declare @resultString nvarchar(100) --存储拆分后的字符
declare @application_num varchar(50),@inventor nvarchar(255)
declare cs_db cursor for
select application_num,inventor from Patents_biomarker
open cs_db
fetch cs_db into @application_num,@inventor
while @@fetch_status=0
begin
set @strSplitString = @inventor
SET @strSplitString = @strSplit + @strSplitString + @strSplit
Set @location = CHARINDEX(@strSplit,@strSplitString)
BEGIN TRAN --开始事务处理
WHILE @location <> 0
BEGIN
SET @strat = @location
SET @location = CHARINDEX(@strSplit,@strSplitString,@strat+1)
IF @location > 0
BEGIN
SET @resultString = subString(@strSplitString,@strat+1,@location-@strat-1)--拆分后的字符
insert into tempvalue
select @application_num,@resultString
END
END
IF @@error = 0
BEGIN
COMMIT TRAN -- 提交
END
ELSE
BEGIN
ROLLBACK TRAN --出错则回滚
END
fetch cs_db into @application_num,@inventor
end
close cs_db
deallocate cs_db
GO
