udfGetFilenameAndExtOnly()

Here’s a simple user-defined function (UDF) to return the filename and extension, when you are passed in the whole path with filename.

For example, this

c:\Program Files\Microsoft SQL Server\MSSQL10.HOLD\MSSQL\DATA\MSDBData.mdf

returns this

MSDBData.mdf

This function will be referenced by the next post.

USE Admin

GO

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

      3/3/12                  PPaiva            Initial creation.

      DESCRIPTION

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

      USAGE

            SELECT dbo.udfGetFilenameAndExtOnly('c:\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('c:\Program Files\DATA\MSDBData.mdf'))

            -- Pos of last backslash

            SELECT Len('c:\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