sql server将字符串分割成表函数strsplitetotable(二)
ata
ELSE IF ( @colNo=9 )
SET @I=@colData
ELSE IF ( @colNo=10 )
SET @J=@colData
ELSE IF ( @colNo=11 )
SET @K=@colData
ELSE IF ( @colNo=12 )
SET @L=@colData
ELSE IF ( @colNo=13 )
SET @M=@colData
ELSE IF ( @colNo=14 )
SET @N=@colData
ELSE IF ( @colNo=15 )
SET @O=@colData
IF ( @colEnd>0 )
BEGIN
SET @colNo=@colNo+1
SET @colStart=@colEnd+1
SET @colEnd= CHARINDEX(@columnSplitChar,@strList,@colStart)
END
ELSE
BEGIN
SET @LastFlag=0
END
END
INSERT INTO @tempTable VALUES(@A, @B, @C, @D,
@E, @F, @G, @H,
@I, @J, @K, @L,
@M, @N, @O)
END
ELSE
BEGIN
--多行多列
SET @rowStart=1
SET @rowEnd= CHARINDEX(@rowSplitChar,@strList,@rowStart)
WHILE(@rowEnd>0 OR @LastFlag>0)
BEGIN
IF ( @rowEnd>0 )
SET @strData =SUBSTRING(@strList,@rowStart,@rowEnd-@rowStart)
ELSE
SET @strData =SUBSTRING(@strList,@rowStart,8000)
--------------获取列开始
SET @colNo=1
SET @A =''
SET @B =''
SET @C =''
SET @D =''
SET @E =''
SET @F =''
SET @G =''
SET @H =''
SET @I =''
SET @J =''
SET @K =''
SET @L =''
SET @M =''
SET @N =''
SET @O =''
SET @LastFlag2=1
SET @colStart=1
SET @colEnd= CHARINDEX(@columnSplitChar,@strData,@colStart)
WHILE(@colEnd>0 OR @LastFlag2>0 )
BEGIN
IF ( @colEnd>0 )
SET @colData =SUBSTRING(@strData,@colStart,@colEnd-@colStart)
ELSE
SET @colData =SUBSTRING(@strData,@colStart,8000)
IF ( @colNo=1 )
SET @A=@colData
ELSE IF ( @colNo=2 )
SET @B=@colData
ELSE IF ( @colNo=3 )
SET @C=@colData
ELSE IF ( @colNo=4 )
SET @D=@colData
ELSE IF ( @colNo=5 )
SET @E=@colData
ELSE IF ( @colNo=6 )
SET @F=@colData
ELSE IF ( @colNo=7 )
SET @G=@colData
ELSE IF ( @colNo=8 )
SET @H=@colData
ELSE IF ( @colNo=9 )
SET @I=@colData
ELSE IF ( @colNo=10 )
SET @J=@colData
ELSE IF ( @colNo=11 )
SET @K=@colData
ELSE IF ( @colNo=12 )
SET @L=@colData
ELSE IF ( @colNo=13 )
SET @M=@colData
ELSE IF ( @colNo=14 )
SET @N=@colData
ELSE IF ( @colNo=15 )
SET @O=@colData
IF ( @colEnd>
0 )
BEGIN
SET @colNo=@colNo+1
SET @colStart=@colEnd+1
SET @colEnd= CHARINDEX(@columnSplitChar,@strData,@colStart)
END
ELSE
BEGIN
SET @LastFlag2=0
END
END
INSERT INTO @tempTable VALUES(@A, @B, @C, @D,
@E, @F, @G, @H,
@I, @J, @K, @L,
@M, @N, @O)
--------------获取列结束
IF ( @rowEnd>0 )
BEGIN
SET @rowStart=@rowEnd+1
SET @rowEnd= CHARINDEX(@rowSplitChar,@strList,@rowStart)
END
ELSE
BEGIN
SET @LastFlag=0
END
END
END
END
END
RETURN
END