Sql Server 2005 截取字符串

 

USE Demo
GO
/*
将表Code的列String中的值提取放到Record表中
String 中字符类型为
dsddddd,2222222,222221,3
其中最后一位为标记对于Record表中的BiaoJi
前面的以','分割的是值对应Record表中Value
*/
GO
DROP PROC proc_split_Code
GO
CREATE PROC  proc_split_Code
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @Count INT  --条数
	DECLARE @Index INT  --变量
	SET @Index = 1      --默认
	SELECT @Count = Count(*) FROM Code
	--PRINT @Count
	WHILE (@Index<=@Count) 
	BEGIN
		DECLARE @BiaoJi INT  -- 标记 
		DECLARE @String NVARCHAR(1000)--字符串
		DECLARE @Temp INT  --分隔符的位置
		DECLARE @Star INT  --开始位置
		DECLARE @Code NVARCHAR(100) --
		SET @Star = 0
		SELECT @String=REVERSE(String)
		FROM (
			SELECT row_number() OVER(ORDER BY String) AS rownumber,* FROM Code
		) AS a
		WHERE rownumber between @Index and @Index
		
		SET @Temp=CHARINDEX(',',@String,@Star)
		SET @BiaoJi = SUBSTRING(@String,@Star,@Temp)
		PRINT @BiaoJi
		SET @String = REVERSE(@String)
		SET @Temp=CHARINDEX(',',@String,@Star)
		SET @Star = 0
		WHILE(@Temp>0)
		BEGIN
			
			SET @Temp=CHARINDEX(',',@String,@Star)

			--PRINT @Star
			--PRINT @Temp
			
			IF @Temp >0
			BEGIN
				SET @Code=SUBSTRING(@String,@Star,@Temp-@Star)
				PRINT @Code
				--插入到相应的表中
				INSERT INTO Record(BiaoJi,Value,Time)
				VALUES (@BiaoJi,@Code,getdate())
				
			END
			SET @Star=@Temp+1
		END			

		--PRINT @Index
		PRINT @String		
		SET @Index = @Index+1
	END
END
GO

EXEC proc_split_Code