SQL Server中实现子字串在父字串中第N次出现的位置， 方便字串解析

create function fn_find(@find varchar(8000), @str varchar(8000), @n smallint) 
   returns int
as
begin
    if @n < 1 return (0)
    declare @start smallint, @count smallint, @index smallint, @len smallint
    set @index = charindex(@find, @str)
    if @index = 0 return (0)
    else select @count = 1, @len = len(@find)
    while @index > 0 and @count < @n
        begin
            set @start = @index + @len
            select @index = charindex(@find, @str, @start), @count = @count + 1
        end
    if @count < @n set @index = 0
    return (@index)
end

 

用例如下：

declare @v_str nvarchar(100)
set @v_str=N'610000173/要抓的中间内容好/要抓的最后內容'
select charindex('/',@v_str) A,  dbo.fn_find('/',@v_str,1) f,dbo.fn_find('/',@v_str,2) s,
substring(@v_str,dbo.fn_find('/',@v_str,1)+1,dbo.fn_find('/',@v_str,2)-dbo.fn_find('/',@v_str,1)-1) result1,
right(@v_str,charindex('/',reverse(@v_str))-1) as result2