dbo.ufn_AT(SearchFor, SearchIn, Occurrence)
dbo.ufn_RAT(SearchFor, SearchIn, Occurrence)
  WHERE
    SearchFor - The CHARACTER expression TO SEARCH FOR.
    SearchIn - The CHARACTER expression TO SEARCH in.
    Occurrence - Which occurrence TO RETURN (DEFAULT IS 1). 

-- ufn_AT ---
IF EXISTS (SELECT * FROM   sysobjects WHERE  name = N'ufn_AT')
	DROP FUNCTION ufn_AT
go
/* Returns the position of one character expression in another
 Parameters:
	@SearchFor  - The character expression to search for
	@SearchIn   - The character expression to search in 
	@Occurrence - Specifies which occurrence of @SearchFor in @SearchIn is returned (1st occurrence is 1)
*/
 
CREATE FUNCTION ufn_AT 
	(@SearchFor VARCHAR(1000), 
	 @SearchIn VARCHAR(8000), 
	 @Occurrence INT = 1) 
RETURNS INT
AS
BEGIN
	DECLARE @Pos INT, @CurrentOccurrence INT
	SET @CurrentOccurrence = 0
	SET @Pos = 0
	WHILE @CurrentOccurrence < @Occurrence
	BEGIN
		SET @Pos = CHARINDEX(@SearchFor, @SearchIn, @Pos+1)
		IF @Pos = 0 BREAK
		SET @CurrentOccurrence = @CurrentOccurrence + 1
	END
	RETURN @Pos
END
go
------------------------------------------------------------------------------------
 
-- ufn_RAT --
 
IF EXISTS (SELECT * FROM   sysobjects WHERE  name = N'ufn_RAT')
	DROP FUNCTION ufn_RAT
go
/* Returns the position of one character expression in another 
		starting from the end of the string
 Parameters:
	@SearchFor  - The character expression to search for
	@SearchIn   - The character expression to search in
	@Occurrence - Which occurrence of @SearchFor in @SearchIn is returned (default is 1)
*/
 
CREATE FUNCTION ufn_RAT 
	(@SearchFor VARCHAR(1000), 
	 @SearchIn VARCHAR(8000), 
	 @Occurrence INT = 1) 
RETURNS INT
AS
BEGIN
	DECLARE @Pos INT, @CurrentOccurrence INT
	-- Search from the right to left by reversing both strings
	SET @SearchFor = REVERSE(@SearchFor)
	SET @SearchIn = REVERSE(@SearchIn)
	SET @CurrentOccurrence = 0
	SET @Pos = 0
	WHILE @CurrentOccurrence < @Occurrence
	BEGIN
		SET @Pos = CHARINDEX(@SearchFor, @SearchIn, @Pos+1)
		IF @Pos = 0 BREAK
		SET @CurrentOccurrence = @CurrentOccurrence + 1
	END
	IF @Pos > 0
		-- Convert to the position from the left
		SET @Pos = DATALENGTH(@SearchIn) - @Pos + 1 - DATALENGTH(@SearchFor) + 1
	RETURN @Pos
END
go

SELECT dbo.ufn_AT('Long', 'Some Long Long Long text', DEFAULT)
SELECT dbo.ufn_RAT('Long', 'Some Long Long Long text', 2)