Transact SQL – File Management – File Names

Background

Unfortunately Transact SQL does not come with rich File Management Tools.

It seemingly lacks native APIs for parsing full file names into folder names, filenames, and extensions.

And, so I always take to the Net when I need to do the above

 

Functions

Here are some scalar functions that I have been able to gather in one place from here and there on the Net.

 

File List

Posted the files to Github and here is the repository.

 

File Name Use
fileManagement.schema.sql Create Schema fileManagement, if it does not exist
fileManagement.udfn_GetDirectoryPath.sql From full file name, get Directory Path
fileManagement.udfn_getFileName.sql From full file name, get File name
fileManagement.udfn_getFileNameExt.sql From full file name, get File Extension
fileManagement.udfn_getFileNameWithoutExt.sql From full file name, get File name without extension

 

 

Modules

Code

fileManagement.udfn_GetDirectoryPath


USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


if schema_id('fileManagement') is null
begin

	exec('create schema [fileManagement] authorization [dbo];' )

end
go

if object_id('[fileManagement].[udfn_GetDirectoryPath]') is null
begin

	exec('CREATE FUNCTION [fileManagement].[udfn_GetDirectoryPath]()
		  RETURNS NVARCHAR(MAX)
	      AS
		  begin
			return 1/0
		  end
		  ')

end
go

ALTER FUNCTION [fileManagement].[udfn_GetDirectoryPath]
(
    @Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @FileName NVARCHAR(MAX)
    DECLARE @ReversedPath NVARCHAR(MAX)
    DECLARE @PathLength INT

    SET @ReversedPath = REVERSE(@Path)
    SELECT @PathLength = CHARINDEX('\', @ReversedPath)
    SELECT @FileName = LEFT(@Path, LEN(@Path) - @PathLength)
    RETURN @FileName
END

go

grant execute on [fileManagement].[udfn_GetDirectoryPath] to public
go

 

fileManagement.udfn_getFileName


USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


if schema_id('fileManagement') is null
begin

	exec('create schema [fileManagement] authorization [dbo];' )

end
go

if object_id('[fileManagement].[udfn_getFileName]') is null
begin

	exec('CREATE FUNCTION [fileManagement].[udfn_getFileName]()
		  RETURNS NVARCHAR(MAX)
	      AS
		  begin
			return 1/0
		  end
		  ')

end
go

ALTER FUNCTION [fileManagement].[udfn_getFileName]
(
    @Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE @FileName NVARCHAR(MAX)
    DECLARE @ReversedPath NVARCHAR(MAX)

    SET @ReversedPath = REVERSE(@Path)
    SELECT @FileName = RIGHT(@Path, CHARINDEX('\', @ReversedPath)-1)

    RETURN @FileName
END

go

grant execute on [fileManagement].[udfn_getFileName] to [public]

go


fileManagement.udfn_getFileNameExt


USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


if object_id('[fileManagement].[udfn_getFileNameExt]') is null
begin

	exec('CREATE FUNCTION [fileManagement].[udfn_getFileNameExt]()
		  RETURNS NVARCHAR(MAX)
	      AS
		  begin
			return 1/0
		  end
		  ')

end
go


ALTER FUNCTION [fileManagement].[udfn_getFileNameExt]
(
    @Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @FileName NVARCHAR(MAX)
    DECLARE @ReversedPath NVARCHAR(MAX)
    DECLARE @ExtLength INT
	DECLARE @ext       varchar(30)

    SET @ReversedPath = REVERSE(@Path)
    SET @FileName = ''
    SELECT @ExtLength = CHARINDEX('.', @ReversedPath)
    IF (@ExtLength > 0) 
    BEGIN 
       SELECT @ext = RIGHT(@Path, @ExtLength - 1)
    END
    RETURN @ext
END
go


grant execute on [fileManagement].[udfn_getFileNameExt] to public
go

fileManagement.udfn_getFileNameWithoutExt


USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if schema_id('fileManagement') is null
begin

	exec('create schema [fileManagement] authorization [dbo];' )

end
go

if object_id('[fileManagement].[udfn_getFileNameWithoutExt]') is null
begin

	exec('CREATE FUNCTION [fileManagement].[udfn_getFileNameWithoutExt]()
		  RETURNS NVARCHAR(MAX)
	      AS
		  begin
			return 1/0
		  end
		  ')

end
go

ALTER FUNCTION [fileManagement].[udfn_getFileNameWithoutExt]
(
    @Path NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @FileName NVARCHAR(MAX)
    DECLARE @ReversedPath NVARCHAR(MAX)
    DECLARE @ExtLength INT

    SET @ReversedPath = REVERSE(@Path)
    SELECT @ExtLength = CHARINDEX('.', @ReversedPath)
    SELECT @FileName = RIGHT(@Path, CHARINDEX('\', @ReversedPath)-1)
    SELECT @FileName = LEFT(@FileName, LEN(@FileName) - @ExtLength)
    RETURN @FileName
END
go


grant execute on [fileManagement].[udfn_getFileNameWithoutExt] to [public]
go


Sample Invocation

Code



declare @filename varchar(400)

set @filename = 'Z:\SQLBackups\ppsivr\FULL\ppsivr_FULL_20170127_115058.bak' 

--display full file name
print '@filename ( full) : ' + [master].fileManagement.udfn_GetDirectoryPath(@filename)

--display directory path
print '@folder : ' + fileManagement.udfn_GetDirectoryPath(@filename)

--display file name
print '@filename : ' + [master].fileManagement.udfn_getFileName(@filename)

--display file name without extenion
print '@filename (without ext): ' + [master].fileManagement.udfn_getFileNameWithoutExt(@filename)

--display file name
print '@ext : ' + fileManagement.udfn_getFileNameExt(@filename)

Output

displayfileinfo

Like You

Like you, I wish back to when things were simpler.

Like HERE ( The Deele – Two Occasions featuring Babyface)

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s