An Easy Way to Get a File Name or a File Extension from a User Defined File Path using Transact-SQL

Download script - 865 B

Introduction

This article will demonstrate how to get a file name or a file extension as well using Transact-SQL.

Background

Several times, developers need to get only the file name or file extension from a file path when they are working on storing the file in binary format into SQL server database, considering the file name and the file extension as a record for further references. Now a days, Microsoft .NET Framework provides some excellent methods for getting the file name or its extension as well. We can consider the scenario where I have an application, it may be windows or web, where I use Visual Basic 6.0 / vbScript and one of the new features will be to store a user defined file in binary format into SQL Server database. So we need to develop a function which will return the file name / extension. In this context, my concern is that it is also possible by using Transact–SQL.

Using the Code

Working with the code is very simple; if you look at the scripts you will see that I used a very common technique which is string reverse logic for doing this. I wrote two stored procedures:

dbo.spGetDocumentName
dbo.spGetDocumentNature

A sample code example is given below:

Example - 1

-- =============================================
-- Author: Md. Marufuzzaman>
-- Create date:
-- Description: Return the file name from a given location.
-- =============================================
/*
DECLARE @VAL VARCHAR(MAX) 
EXEC dbo.spGetDocumentName 'E:\Guideline & Programming Tools & _
    Resources\Resource - 2\Microsoft Visual Basic 6.0 Source\myFile.pdf', @VAL OUTPUT
PRINT @VAL
*/
CREATE PROCEDURE [dbo].[spGetDocumentName]
 -- Add the parameters for the stored procedure here
  @FILE_PATH   VARCHAR(MAX) 
 ,@NAME        VARCHAR(MAX) OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
--DECLARE @FILE_PATH VARCHAR(MAX)
DECLARE @FILE_LENGTH INT
DECLARE @FILE_NAME VARCHAR(MAX)
 
 SET @FILE_LENGTH = LEN(@FILE_PATH)
 WHILE (@FILE_LENGTH <> 0)
 BEGIN
  IF SUBSTRING(@FILE_PATH, @FILE_LENGTH, 1) = '\' 
   BEGIN
    SET @FILE_NAME = RIGHT(@FILE_PATH, LEN(@FILE_PATH) - @FILE_LENGTH)
    BREAK
   END
 SET @FILE_LENGTH = @FILE_LENGTH - 1
 END
   
 SET @NAME = @FILE_NAME
 RETURN 
END
GO

Example - 2

-- =============================================
-- Author: Md. Marufuzzaman>
-- Create date: 
-- Description: Return the nature of the file from a given location.
-- =============================================
/*
DECLARE @VAL VARCHAR(MAX) 
EXEC dbo.spGetDocumentNature'E:\Guideline & Programming Tools & _
    Resources\Resource - 2\Microsoft Visual Basic 6.0 Source\myFile.pdf', @VAL OUTPUT
PRINT @VAL
*/
CREATE PROCEDURE [dbo].[spGetDocumentNature]
 -- Add the parameters for the stored procedure here
  @FILE_PATH   VARCHAR(MAX) 
 ,@EXTN       VARCHAR(10) OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
--DECLARE @FILE_PATH VARCHAR(MAX)
DECLARE @FILE_LENGTH INT
DECLARE @FILE_NATURE VARCHAR(10)
 
 SET @FILE_LENGTH = LEN(@FILE_PATH)
 WHILE (@FILE_LENGTH <> 0)
 BEGIN
  IF SUBSTRING(@FILE_PATH, @FILE_LENGTH, 1) = '.' 
   BEGIN
    SET @FILE_NATURE = RIGHT(@FILE_PATH, LEN(@FILE_PATH) - @FILE_LENGTH)
    BREAK
   END
 SET @FILE_LENGTH = @FILE_LENGTH - 1
 END
    
 SET @EXTN = @FILE_NATURE
 RETURN 
END