Technical Article

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)