FileNameSplitter

,

Use this function to break a full file name into four parts: a drive, a path, a filename, and an extension.  It handles any missing parts.  The function returns a table with the four parts as columns so you can use "cross apply" if needed.

select *

from dbo.FileNameSplitter('D:\MyDir\MySubDir\MyFile.MyExt')

It returns

"D:" as drive

"\MyDir\MySubDir\" as the path

"MyFile" as filename

"MyExt" as extension

OR try this:

SELECT physical_name, x.*

FROM sys.database_files df

cross apply (select * from dbo.FileNameSplitter(df.physical_name)) as x;

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

Rate

3 (3)

Share

Share

Rate

3 (3)