＊＊＊＊字符串分割问题

[OceanRain]

字符串：set @str = '集团/北京(总经理室 General Manager Department)/北京有限公司/朝阳住宅部 East Area Residence Department/朝阳区域/新天地酒'

要求：函数参数(字符串,分割符,从第几位开始截取到最后)
例题：
1.如:取第3个/符号后的所有字符串 函数调用应该是:GetStr(@str, '/',3)
结果应该是： 朝阳住宅部 East Area Residence Department/朝阳区域/新天地酒'
2.若函数的第三个参数大于第二个参数出现的次数，则返回空
如:函数调用应该是:GetStr(@str, '/',10)
结果是:空(因为/在字符串中总共出现5次)

[-狙击手-]

create function f_str(@p varchar(2),@pos int,@s varchar(2000))
returns varchar(1000)
as
begin
    declare @i int
    set @i = charindex(@p,@s)
	if @i = 0 return @s
	while @pos > 0 
    begin
		set @i = charindex(@p,@s)
		if @i = 0 
           if @pos > 0 
				return ''
           else
                return @s
        set @s = stuff(@s,1,@i,'')
        set @pos = @pos - 1
    end
    return @s
end
go


declare @str varchar(1000)     
set @str = '集团/北京(总经理室 General Manager Department)/北京有限公司/朝阳住宅部 East Area Residence Department/朝阳区域/新天地酒' 
select dbo.f_str('/',3,@str)


/*


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

朝阳住宅部 East Area Residence Department/朝阳区域/新天地酒

*/


select dbo.f_str('/',13,@str)

/*

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


(1 行受影响)
*/
drop function f_str

[ljhcy99]

create function Fucn(@str varchar(100), @split varchar(1),num int )
as
begin
declare @varStr varchar(100);
declare @cnt int;
declare @Result varchar(100);
set @Result = '';
if @str = '' then return @Result;
set @varStr=@Str
while (charindex(@split,@varStr) >0)
begin
set @cnt =@cnt+1;
set @varStr =right(@varStr, len(@varStr)-charindex(@split,@varStr) );
end
if @cnt < num then return @Result;
@Result = right(@varStr, len(@varStr)- num);
return @Result;
end

[csdyyr]

create function f_GetStr(@str nvarchar(100), @spliter nvarchar(1), @count int)
returns nvarchar(100)
as
begin
    declare @s nvarchar(100)
    declare @num int
    set @s=@str
    set @num=0
    while charindex(@spliter, @str)>0 
    begin
        set @num=@num+1       
        set @str=stuff(@str, 1, charindex(@spliter, @str), '')
         if @count=@num
             set @s=@str
    end
    if @count >@num
        set @s= ''
    return @s
end
go

declare @str nvarchar(100)
set @str = N'集团/北京(总经理室 General Manager Department)/北京有限公司/朝阳住宅部 East Area Residence Department/朝阳区域/新天地酒' 

select dbo.f_GetStr(@str, '/', 3)
/*
朝阳住宅部 East Area Residence Department/朝阳区域/新天地酒
*/
select dbo.f_GetStr(@str, '/', 10)

drop function f_GetStr

[ljhcy99]

---函数
IF OBJECT_ID (N'dbo.Fucn', N'FN') IS NOT NULL
DROP FUNCTION dbo.Fucn;
GO
CREATE FUNCTION dbo.Fucn ((@str varchar(100), @split varchar(1),num int )
RETURNS varchar(100)
AS
BEGIN
----- 变量定义
declare @varStr varchar(100);
declare @cnt int;
declare @Result varchar(100);
set @Result = '';
set @cnt = 0;
----- 输入串为空 则退出
if @str = ''
begin
return @Result;
end
----- 查找 分隔符 出现的次数
set @varStr=@Str;
while (charindex(@split,@varStr) >0)
begin
set @cnt =@cnt+1;
set @varStr =right(@varStr, len(@varStr)-charindex(@split,@varStr) );
end
-----分隔符 出现的次数 小于输入参数 则退出
if @cnt < num
begin
return @Result;
end
----截取字符串
@Result = right(@varStr, len(@varStr)- num);
return @Result;

END;