USE [alisoft]
GO
/****** Object: UserDefinedFunction [dbo].[split] Script Date: 09/22/2011 08:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
[调用]:
select * From split('12131:316631,44:5353,4322:323,443:333',',',':')
select * From split('12131,44,4322,443',',','')
[功能]:通过分格符区分
[详细逻辑]:
@source 源数据
@splits 第一个分格符
@splits2 第二个分格符
支持第二个分格符为空
[作者]:XLP
[版本号]:v1.0
[最后修改时间]:2011-09-22
[历史修改记录]
-------------------
修改人:
修改原因:
修改内容:
-------------------
*/
ALTER FUNCTION [dbo].[split](
@source varchar(8000),--源数据
@splits varchar(100),--分格符
@splits2 varchar(100)--第二分格符
)
RETURNS @tmp TABLE(
ID int IDENTITY PRIMARY KEY, --自填列
short_str varchar(8000),
short_str2 varchar(8000)
)
AS
BEGIN
DECLARE @source_Tmp varchar(8000),
@short_str varchar(8000),
@short_str1 varchar(8000),
@short_str2 varchar(8000),
@splits_length int
SET @splits_length = LEN(@splits)
IF CHARINDEX(@splits,@source)=1
SET @source_Tmp=SUBSTRING(@source,@splits_length+1,LEN(@source)-@splits_length)
ELSE
SET @source_Tmp=@source
IF CHARINDEX(REVERSE(@splits),REVERSE(@source_Tmp))>1
SET @source_Tmp=@source_Tmp+@splits
ELSE
SET @source_Tmp=@source_Tmp
WHILE CHARINDEX(@splits,@source_Tmp)>0
BEGIN
SET @short_str=SUBSTRING(@source_Tmp,1,CHARINDEX(@splits,@source_Tmp)-1)
DECLARE @source_Tmp_LEN INT,@splits_Position_END int
SET @source_Tmp_LEN = LEN(@source_Tmp)
SET @splits_Position_END = LEN(@short_str)+@splits_length
SET @source_Tmp=REVERSE(SUBSTRING(REVERSE(@source_Tmp),1,@source_Tmp_LEN-@splits_Position_END))
IF CHARINDEX(@splits2,@short_str)>0
begin
set @short_str1=SUBSTRING(@short_str,0,CHARINDEX(@splits2,@short_str))
set @short_str2=SUBSTRING(@short_str,CHARINDEX(@splits2,@short_str)+1,len(@short_str))
INSERT INTO @tmp SELECT @short_str1,@short_str2
end
else
begin
IF @short_str<>'' INSERT INTO @tmp SELECT @short_str ,''
end
END
RETURN
END
GO
/****** Object: UserDefinedFunction [dbo].[split] Script Date: 09/22/2011 08:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
[调用]:
select * From split('12131:316631,44:5353,4322:323,443:333',',',':')
select * From split('12131,44,4322,443',',','')
[功能]:通过分格符区分
[详细逻辑]:
@source 源数据
@splits 第一个分格符
@splits2 第二个分格符
支持第二个分格符为空
[作者]:XLP
[版本号]:v1.0
[最后修改时间]:2011-09-22
[历史修改记录]
-------------------
修改人:
修改原因:
修改内容:
-------------------
*/
ALTER FUNCTION [dbo].[split](
@source varchar(8000),--源数据
@splits varchar(100),--分格符
@splits2 varchar(100)--第二分格符
)
RETURNS @tmp TABLE(
ID int IDENTITY PRIMARY KEY, --自填列
short_str varchar(8000),
short_str2 varchar(8000)
)
AS
BEGIN
DECLARE @source_Tmp varchar(8000),
@short_str varchar(8000),
@short_str1 varchar(8000),
@short_str2 varchar(8000),
@splits_length int
SET @splits_length = LEN(@splits)
IF CHARINDEX(@splits,@source)=1
SET @source_Tmp=SUBSTRING(@source,@splits_length+1,LEN(@source)-@splits_length)
ELSE
SET @source_Tmp=@source
IF CHARINDEX(REVERSE(@splits),REVERSE(@source_Tmp))>1
SET @source_Tmp=@source_Tmp+@splits
ELSE
SET @source_Tmp=@source_Tmp
WHILE CHARINDEX(@splits,@source_Tmp)>0
BEGIN
SET @short_str=SUBSTRING(@source_Tmp,1,CHARINDEX(@splits,@source_Tmp)-1)
DECLARE @source_Tmp_LEN INT,@splits_Position_END int
SET @source_Tmp_LEN = LEN(@source_Tmp)
SET @splits_Position_END = LEN(@short_str)+@splits_length
SET @source_Tmp=REVERSE(SUBSTRING(REVERSE(@source_Tmp),1,@source_Tmp_LEN-@splits_Position_END))
IF CHARINDEX(@splits2,@short_str)>0
begin
set @short_str1=SUBSTRING(@short_str,0,CHARINDEX(@splits2,@short_str))
set @short_str2=SUBSTRING(@short_str,CHARINDEX(@splits2,@short_str)+1,len(@short_str))
INSERT INTO @tmp SELECT @short_str1,@short_str2
end
else
begin
IF @short_str<>'' INSERT INTO @tmp SELECT @short_str ,''
end
END
RETURN
END