经常把存储过程(含参数)中取得片接的大字符串(字符串1;字符串2;字符串3;)进行分割再处理~
比如,其他系统通过WebService接口调用,传入大串工号片接字符串(K17000205;K17000658;K16002598;K17001422),程式通过以下逻辑进行分割,返回Table对象.
函数代码如下
-- =============================================
-- Author: <WH>
-- Create date: <2019/01/01>
-- Description: <依据分割符对字符串进行分割,返回Table(表)对象>
-- =============================================
create FUNCTION [dbo].[F_Split]
(
@SplitString nvarchar(max), --源字符串
@Separator nvarchar(10)=' ' --分隔符号,默认为空格
)
RETURNS @SplitStringsTable TABLE --输出的数据表
(
[id] int identity(1,1),
[value] nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max);
SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
执行生成小程式,如下:
演示上述小程式的调用,代码如下:
DECLARE @EmpIDStr nvarchar(max)
set @EmpIDStr ='K17000205;K17000658;K16002598;K17001422'
SELECT * INTO #T1 FROM F_Split(@EmpIDStr, ';')
select * from #T1
--处理结束
DROP TABLE #T1
运行效果,如下