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