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.

Wednesday, December 29, 2010

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


No comments:

Post a Comment