udfGetFilenameOnly()

These posts are a couple of very helpful scalar user-defined functions to help split up those long path names.  They may be referenced on occasion in subsequent posts on this blog.

The names are self-explanatory.  

USE Admin

GO

IF Object_ID('dbo.udfGetFilenameOnly') Is Not Null

      DROP FUNCTION dbo.udfGetFilenameOnly

go

CREATE FUNCTION dbo.udfGetFilenameOnly(

      @In varchar(500)

      )

RETURNS varchar(300)

AS

/*    DATE        AUTHOR            REMARKS

      12/29/10    PPaiva            Initial creation.

      USAGE

            SELECT dbo.udfGetFilenameOnly('D:\Program Files\Microsoft SQL Server\Logs\Admin_Log.LDF')

            SELECT dbo.udfGetFilenameOnly('Admin_Log.ldf')

*/

BEGIN

      DECLARE @Out varchar(300),

                  @Pos smallint

           

      -- Get position of last backslash

      SET @Pos = CharIndex('\', Reverse(@In))

      IF @Pos > 1

            BEGIN

                  SET @Pos = Len(@In) - @Pos               

                  SET @Out = Substring(@In, @Pos + 2, 500)

            END

     

      ELSE

            SET @Out = @In

      RETURN @Out

END