——————————————————————————————————————————————————————————————————————————————————————————————
传入字符串拿分隔符分开后
/*
函数: Erp.CharDelimTable(@ParamStr,@DelimStr)
作用: 将传入字符串拿分隔符分开后放到返回表里
测试:
select * from Erp.CharDelimTable('1,8,,8,8,8',',')
select * from Erp.CharDelimTable('1,8,,8,8,8',',') where IsNumeric(ParamValue) = 0
select * from Erp.CharDelimTable('123',',')
返回: 保存传入字符串拿分隔符分开后元素的Table
调用: 无调用
*/
Alter Function [Erp].[CharDelimTable]
(
@ParamStr nvarchar(max),
@DelimStr nvarchar(20)
)
Returns @CharDelimTable Table (
ID decimal(18, 0) IDENTITY(1,1) NOT NULL,
ParamValue nvarchar(max) NULL
)
As
begin
if(Len(@ParamStr) = 0)
return
else
begin
if(CharIndex(@DelimStr,@ParamStr) = 0)
begin
insert into @CharDelimTable(ParamValue)
select @ParamStr as ParamValue
end
else
begin
while(CharIndex(@DelimStr,@ParamStr) > 0)
begin
insert into @CharDelimTable(ParamValue)
select SubString(@ParamStr,0,CharIndex(@DelimStr,@ParamStr)) as ParamValue
set @ParamStr = SubString(@ParamStr,CharIndex(@DelimStr,@ParamStr)+1,Len(@ParamStr))
end
insert into @CharDelimTable(ParamValue)
select @ParamStr as ParamValue
end
end
return
end