存储过程拆分Patents表Inventor字段逗号分隔字符串

USE [METRIX]
GO


/****** Object:  StoredProcedure [dbo].[SplitString]    Script Date: 11/12/2013 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