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