方法一 OriginalStr 字符串中间用,分割 SplitChar 要分割的符合
调用方法如下 示例 splitvalue 是'fnc_split' 中临时表中的字段 @configvaluestr 整个字符串 billnamestr 要查找的字符串
if((Select splitvalue From dbo.fnc_split(@configvaluestr,',') where splitvalue=@billnamestr) is not null)
begin
insert into @BusinessCashRequirementTB select 1--业务请款
end
else if((Select splitvalue From dbo.fnc_split(@noconfigvaluestr,',') where splitvalue=@billnamestr)is not null)
begin
insert into @BusinessCashRequirementTB select 0--非业务请款
end
IF OBJECT_ID('fnc_split') IS NOT NULL
BEGIN
DROP function fnc_split
PRINT '<<< DROPPED PROCEDURE fnc_split >>>'
END
GO
Create function [dbo].[fnc_split]
(
@OriginalStr nvarchar(4000),
@SplitChar nvarchar(1000)
)
--定义函数回传值类型为：Table
returns @temp table(splitValue nvarchar(1000))
As
Begin
declare @index int
declare @s nvarchar(1000)
--通過While迴圈得到分隔之後的字串
while(1=1)
Begin
--取得分隔字串在來源字中的位置
set @index=charindex(@SplitChar,@OriginalStr)
if @index=0
begin
insert into @temp(splitValue) values(@OriginalStr)
break;
End
--借助於Left函数将来源字串截斷
set @s=left(@OriginalStr,@index-1)
insert into @temp(splitValue) values(@s)
set @OriginalStr=Right(@OriginalStr,len(@OriginalStr)-@index)
End
return
End
方法二
传configvaluestr字符串中间用,分割 billnamestr要分割的字符串
调用方法如下
if((Select splitStr From dbo.F_CC_SplitString(@configvaluestr,@billnamestr)) is not null)
begin
insert into @BusinessCashRequirementTB select 1--业务请款
end
ALTER FUNCTION [dbo].[F_CC_SplitString]
(
@string varchar(MAX),
@splitChar varchar(10)
)
RETURNS @table TABLE(splitStr varchar(max))
AS
BEGIN
DECLARE @i int
set @string=rtrim(ltrim(@string))
SET @i = charindex(@splitChar, @string)
WHILE @i > 1
BEGIN
INSERT @table VALUES(LEFT(@string, @i-1))
SET @string = substring(@string, @i+1 , len(@string)-@i)
SET @i = charindex(@splitChar, @string)
END
IF @string <> ''
INSERT @table VALUES(@string)
RETURN
END
