FileNameSplitter

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.FileNameSplitter') AND type in (N'FN', N'TF'))
DROP FUNCTION dbo.FileNameSplitter
GO

CREATE FUNCTION dbo.FileNameSplitter
(
@FullName varchar(max)
)
RETURNS @Parts TABLE 
(
Drive varchar(2),
FullPath varchar(max),
RootFileName varchar(255),
Extension varchar(255)
)
AS
BEGIN
-- written by William Talada
DECLARE
@Drive varchar(250),
@FullPath varchar(250),
@RootFileName varchar(250),
@Extension varchar(250),
@dot int,
@slash int
;

-- DRIVE
IF PATINDEX('[A-Z]:', LEFT(@FullName,2)) = 1
BEGIN
SET @Drive = LEFT(@FullName,2);
SET @FullName = RIGHT(@FullName, LEN(@FullName) - 2);
END

-- EXTENSION
SET @dot = CHARINDEX('.', Reverse(@FullName));
SET @slash = CHARINDEX('\', Reverse(@FullName));

IF (@dot = 0) GoTo NoExtension

IF ((@dot > @slash) and (@slash > 0)) GoTo NoExtension;

IF (@dot > 0)
BEGIN
SET @Extension = RIGHT(@FullName, @dot - 1);
SET @FullName = LEFT(@FullName, LEN(@FullName) - @dot);
END

NoExtension:

-- RootFileName
SET @slash = CHARINDEX('\', Reverse(@FullName));

IF @slash > 0
BEGIN
SET @RootFileName = RIGHT(@FullName, @slash - 1);
SET @FullName = LEFT(@FullName, LEN(@FullName) - @slash + 1);
END
ELSE
BEGIN
SET @RootFileName = @FullName;
SET @FullName = NULL;
END

-- FullPath
SET @FullPath = @FullName;

-- done
INSERT INTO @Parts
SELECT 
ISNULL(@Drive,''),
ISNULL(@FullPath,''),
ISNULL(@RootFileName,''),
ISNULL(@Extension,'')
;

RETURN
END
GO