Performance of .Net Code in SQL Server 2005

CREATE FUNCTION dbo.udf_Txt_CharIndexRev (
 
     @SearchFor varchar(255) -- Sequence to be found
   , @SearchIn varchar(8000) -- The string to be searched
 )  RETURNS int -- Position from the back of the string where
                -- @SearchFor is found in @SearchIn
     WITH SCHEMABINDING
 /*
 * Searches for a string in another string working from the back.
 * It reports the position (relative to the front) of the first
 * such expression it finds. If the expression is not found, it
 * returns zero.
 *
 * Equivalent Template:
        CASE
          WHEN CHARINDEX(
                       , ) > 0
          THEN LEN()
               - CHARINDEX(REVERSE()
                   , REVERSE ()) + 1
          ELSE 0 END 
 * Example:
 select dbo.udf_Txt_CharIndexRev('\', 'C:\temp\abcd.txt')
 *
 * Test:
 PRINT 'Test 1    ' + CASE WHEN 8=
            dbo.udf_Txt_CharIndexRev ('\', 'C:\temp\abcd.txt')
                      THEN 'Worked' ELSE 'ERROR' END
 PRINT 'Test 2    ' + CASE WHEN 0=
            dbo.udf_Txt_CharIndexRev ('*', 'C:\tmp\d.txt')
                      THEN 'Worked' ELSE 'ERROR' END
 --                                     12345678901234567890
 PRINT 'Test 3    ' + CASE WHEN 18=
        dbo.udf_Txt_CharIndexRev('fj', 'f123 asdasfdfdfddfjas  ')
                      THEN 'Worked' ELSE 'ERROR' END
 *
 * History
 * WHEN       WHO        WHAT
 * ---------- ---------- -----------------------------------------
 * 2002-12-10 ASN        Initial Coding
 * 2003-06-16 ASN        Fixed problem with Test 3 by using
                         DATALENGTH instead of LEN
 ****************************************************************/
 AS BEGIN
 
     DECLARE @Result int
           , @StringLen int
           , @ReverseIn varchar(8000)
           , @ReverseFor varchar(255)
 
     SELECT @ReverseIn = REVERSE (@SearchIn)
          , @ReverseFor = REVERSE(@SearchFor)
          , @StringLen = DATALENGTH(@SearchIn)
 
     SELECT @Result = CHARINDEX(@ReverseFor, @ReverseIn)
    
     -- return the position from the front of the string                         
     IF @Result > 0
         SET @Result = @StringLen - (@Result
                                   + DATALENGTH(@SearchFor)
                                   - 2
                                    )
     -- ENDIF
 
     RETURN @Result
 END
 GO
 GRANT EXEC on dbo.udf_Txt_CharIndexRev to PUBLIC
 GO

The VB.Net version is named CharIndexRev and it takes advantage of the built-in function InStrRev that implements this function.  InStrRev is from the Microsoft.VisualBasic compatibility library, which consists of functions that existed in Visual Basic 5, but which have different names in the .Net Common Language Runtime (CLR).  Here’s the VB.Net function:

Imports System
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic

Partial Public Class UserDefinedFunctions

''' <summary>
''' Returns the position of the last occurrence of SearchFor in SearchIn
''' </summary>
''' <param name="SearchFor">String being located</param>
''' <param name="SearchIn">String being searched</param>
''' <returns>Position, 1 based relative to the start of the string
''' of the last occurrence of SearchFor in SearchIn</returns>
''' <remarks></remarks>
<SqlFunction()> _
Public Shared Function CharIndexRev(ByVal SearchFor As SqlString, _
                                    ByVal SearchIn As SqlString _
                                   ) As SqlInt32

    Return InStrRev(CType(SearchIn, String), CType(SearchFor, String))

End Function
End Class