CREATE FUNCTION [dbo].[udf_LAST_SPACE]
	(@str varchar(8000))
RETURNS int
AS
BEGIN
	DECLARE @Words INT, @Pos INT, @x Int, @y Int
	SELECT @Words = 0, @Pos = 1, @x = -1

	WHILE (@x <> 0)
		BEGIN
			SET @y = @x
			SET @x = CHARINDEX(' ', @str, @Pos)
			SET @Pos = @x + 1
			SET @Words = @Words + 1
		END

	RETURN @y
END

USE [dba]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_FIND_OFFSET]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_FIND_OFFSET]
GO

CREATE FUNCTION [dbo].[udf_FIND_OFFSET]
	(@str varchar(8000),@target varchar(8000),@offset int)
RETURNS int
AS
BEGIN
	DECLARE @Words INT, @Pos INT, @x Int, @y Int
	SELECT @Words = 0, @Pos = 1, @x = -1

	WHILE (@Words <= @offset)
		BEGIN
			SET @y = @x
			SET @x = CHARINDEX(@target, @str, @Pos)
			SET @Pos = @x + 1
			SET @Words = @Words + 1
		END

	RETURN @y
END
GO

DECLARE @t table(Col1 varchar(8000))

INSERT INTO @t(Col1)
SELECT 'abc\xyz\total\endreport'

SELECT *, dbo.[udf_FIND_OFFSET](Col1,'\',2) FROM @t
GO