SQL函数:分离字符串,并返回分离后字符的表
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
-- =============================================
-- Author: 苏宗伟
-- Create date: 2008-8-1
-- Description: 分离字符串,并返回分离后字符的表
-- =============================================
ALTER FUNCTION [dbo].[SplitStr]
(
-- Add the parameters for the function here
@Str as nvarchar(2048), --需分离的字符串
@Split as nvarchar(50)--分离标志
)
RETURNS @Reports TABLE(subStr nvarchar(200))
AS
-- Add the SELECT statement with parameter references here
begin
declare @index int --查询位置
declare @start int --匹配位置
declare @end int --分割符长度
declare @subStr varchar(255) --分离后的字符
set @index = 0;
while(len(@Str)>0)
begin
set @start = charindex(@Split,@Str,@index);--查询分割符的起始位置
set @end = len(@Split)
if(@start>0)--表示含有该分割符
begin
--分离子字符串,并将分离后的剩余字符赋值于@Str,以便下次处理
select @subStr = substring(@Str,0,charindex(@Split,@Str,@index));
select @Str = substring(@Str,@start+@end,len(@Str));
end
else
begin
select @subStr=@Str;
select @Str='';
end
if(@subStr!='')--值不为空,做插入操作
insert @Reports select @subStr
end
Return
end