sql server 将字符串分割成表函数 strsplitetotable
在sql server里，调用存储过程时，经常需要将数据拼成字符串做为参数调用存储过程，而在储存过程中分割字符串虽然简单但麻烦，封装了该函数，可以将拼串分割成内存表返回，方便使用，返回的表字段从a,b,c,d,e开始，最多支持15列
调用方式：select * from flfcp_StrSplitToTable('a,b,c|e,f,g', ',', '|')
返回结果：
调用方式：select * from flfcp_StrSplitToTable('a,b,c,e,f,g', ',', '')
返回结果：
调用方式：select * from flfcp_StrSplitToTable('a,b,c,e,f,g', '', ',')
返回结果：
/****** Object: UserDefinedFunction [dbo].[flfcp_StrSplitToTable] Script Date: 08/05/2013 20:29:07 ******/
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[flfcp_StrSplitToTable](@strList VARCHAR(5000), @columnSplitChar VARCHAR(1), @rowSplitChar VARCHAR(1))
RETURNS @tempTable TABLE(ID INT IDENTITY(1,1) PRIMARY KEY,
[A] [varchar](100) NULL,
[B] [varchar](100) NULL,
[C] [varchar](100) NULL,
[D] [varchar](100) NULL,
[E] [varchar](100) NULL,
[F] [varchar](100) NULL,
[G] [varchar](100) NULL,
[H] [varchar](100) NULL,
[I] [varchar](100) NULL,
[J] [varchar](100) NULL,
[K] [varchar](100) NULL,
[L] [varchar](100) NULL,
[M] [varchar](100) NULL,
[N] [varchar](100) NULL,
[O] [varchar](100) NULL )
AS
BEGIN
DECLARE @A varchar(100)
DECLARE @B varchar(100)
DECLARE @C varchar(100)
DECLARE @D varchar(100)
DECLARE @E varchar(100)
DECLARE @F varchar(100)
DECLARE @G varchar(100)
DECLARE @H varchar(100)
DECLARE @I varchar(100)
DECLARE @J varchar(100)
DECLARE @K varchar(100)
DECLARE @L varchar(100)
DECLARE @M varchar(100)
DECLARE @N varchar(100)
DECLARE @O varchar(100)
DECLARE @colNo INT -- 列的序号
DECLARE @colStart INT -- 列开始查询位置
DECLARE @colEnd INT -- 列查找到的位置
DECLARE @strData VARCHAR(2000) -- 内容
DECLARE @colData VARCHAR(2000) -- 列内容
DECLARE @rowStart INT -- 行开始查询位置
DECLARE @rowEnd INT -- 行查找到的位置
DECLARE @strLen INT -- 字符串长度
DECLARE @LastFlag INT -- 最后一行标志
DECLARE @LastFlag2 INT -- 最后一行标志
SET @LastFlag=1
SET @LastFlag2=1
IF ( LEN(ISNULL(@strList,''))>0 )
BEGIN
IF ( LEN(ISNULL(@columnSplitChar,''))=0 )
BEGIN --列为空的情况
IF ( LEN(ISNULL(@rowSplitChar,''))=0 )
BEGIN
-- 一行一列
INSERT INTO @tempTable (A) VALUES(@strList)
END
ELSE
BEGIN
-- 多行一列
SET @rowStart=1
SET @rowEnd= CHARINDEX(@rowSplitChar,@strList,@rowStart)
WHILE(@rowEnd>0 )
BEGIN
SET @strData =SUBSTRING(@strList,@rowStart,@rowEnd-@rowStart)
INSERT INTO @tempTable (A) VALUES(@strData)
SET @rowStart=@rowEnd+1
SET @rowEnd= CHARINDEX(@rowSplitChar,@strList,@rowStart)
END
--最后行
SET @strData =SUBSTRING(@strList,@rowStart,8000)
INSERT INTO @tempTable (A) VALUES(@strData)
END
END
ELSE
BEGIN --列不为空的情况
IF ( LEN(ISNULL(@rowSplitChar,''))=0 )
BEGIN
--一行多列
SET @colNo=1
SET @colStart=1
SET @colEnd= CHARINDEX(@columnSplitChar,@strList,@colStart)
WHILE(@colEnd>0 OR @LastFlag>0 )
BEGIN
IF ( @colEnd>0 )
SET @colData =SUBSTRING(@strList,@colStart,@colEnd-@colStart)
ELSE
SET @colData =SUBSTRING(@strList,@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,@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
