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