udfGetFilenameAndExtOnly

Useful function for returning only the filename with its extension.  That is, the path is not shown.

This is a dependent object for some other objects on this blog.

USE Admin

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

      DROP FUNCTION dbo.udfGetFilenameAndExtOnly

go

CREATE FUNCTION dbo.udfGetFilenameAndExtOnly(

      @In varchar(1000)

      )

RETURNS varchar(300)

AS

/*    DATE              AUTHOR            REMARKS

      8/22/14          PPaiva            Initial creation.

      DESCRIPTION

            Returns filename and extention only.  That is, path is stripped away.

      USAGE

            SELECT dbo.udfGetFilenameAndExtOnly('O:\Program Files\Microsoft SQL Server\MSSQL10.HOLD\MSSQL\DATA\MSDBData.mdf')

            SELECT dbo.udfGetFilenameAndExtOnly('Admin_Log.ldf')

      DEBUG

            -- Last backslash, in reverse

            SELECT CharIndex('\', Reverse('O:\Program Files\DATA\MSDBData.mdf'))

            -- Pos of last backslash

            SELECT Len('O:\Program Files\DATA\MSDBData.mdf') - 13

*/

BEGIN

      DECLARE @Out varchar(300),

                  @Pos smallint

           

      -- Strip away path if it exists

      -- Get position of last backslash

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

      IF @Pos > 1

            BEGIN

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

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

            END

      ELSE

            SET @Out = @In

      RETURN @Out

END