SqlServer2005中字符函数的应用

USEDemo
GO
/*
将表Code的列String中的值提取放到Record表中
String中字符类型为
dsddddd,2222222,222221,3
其中最后一位为标记对于Record表中的BiaoJi
前面的以","分割的是值对应Record表中Value
*/
GO
DROPPROCproc_split_Code
GO
CREATEPROCproc_split_Code
AS
BEGIN
SETNOCOUNTON

DECLARE@CountINT--条数
DECLARE@IndexINT--变量
SET@Index=1--默认
SELECT@Count=Count(*)FROMCode
--PRINT@Count
WHILE(@Index<=@Count)
BEGIN
DECLARE@BiaoJiINT--标记
DECLARE@StringNVARCHAR(1000)--字符串
DECLARE@TempINT--分隔符的位置
DECLARE@StarINT--开始位置
DECLARE@CodeNVARCHAR(100)--
SET@Star=0
SELECT@String=REVERSE(String)
FROM(
SELECTrow_number()OVER(ORDERBYString)ASrownumber,*FROMCode
)ASa
WHERErownumberbetween@Indexand@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
--插入到相应的表中
INSERTINTORecord(BiaoJi,Value,Time)
VALUES(@BiaoJi,@Code,getdate())

END
SET@Star=@Temp+1
END

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

EXECproc_split_Code