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

udfGetPathOnly()

This is a companion to the last post.  This scalar user-defined function helps with splitting up those long path names by extracting the path only.  This may be referenced on occasion in subsequent posts on this blog.

USE Admin
GO
IF Object_ID('dbo.udfGetPathOnly') Is Not Null
      DROP FUNCTION dbo.udfGetPathOnly
go

CREATE FUNCTION dbo.udfGetPathOnly(
@In varchar(1000)
)
RETURNS varchar(1000)
AS
/*    DATE        AUTHOR            REMARKS
      12/29/10    PPaiva            Initial creation.


      DESCRIPTION
            Returns the path only, with a trailing backslash. 

      USAGE
            SELECT dbo.udfGetPathOnly('C:\Windows\Notepad.exe')
            SELECT dbo.udfGetPathOnly('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\tempdb.mdf')
            SELECT dbo.udfGetPathOnly('tempdb.ldf')
            SELECT dbo.udfGetPathOnly('C:\asdf')
            SELECT dbo.udfGetPathOnly('C:')
            SELECT dbo.udfGetPathOnly('Z:')

*/

BEGIN
      DECLARE @Out varchar(300),
                  @PosSlash smallint,
                  @PosDot smallint


      -- Get position of last backslash
      SET @PosSlash = CharIndex('\', Reverse(@In))
      -- Get position of last dot
      SET @PosDot = CharIndex('.', Reverse(@In))

      IF @PosDot > 0
            SET @PosDot = Len(@In) - @PosDot + 1

      IF @PosSlash > 0
            BEGIN
                  SET @PosSlash = Len(@In) - @PosSlash + 1
                  IF @PosDot = 0
                        SET @Out = @In
                  ELSE
                        SET @Out = Left(@In, @PosSlash)
            END

      ELSE
            IF Len(@In) = 2 AND Right(@In, 1) = ':'
                  SET @Out = @In + '\'


      RETURN @Out

END

No comments:

Post a Comment