About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Friday, August 22, 2014

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