Extracting the filename from a full path in SQL Server

We are getting the files and we are loading the file data in the database table also we need to load the file name as the document name.

For example

We have a table in that we are loading the document data and document name. For the document name we are extracting the file name from the full path.

Below is the table.

CREATE TABLE file_details(                 fileid int IDENTITY(1,1) NOT NULL,                 file_nm varchar(500) NULL,                 file_data varbinary(max) NULL )

Below is the source folder  

Below is the script to extract the file name from the full path and loading that data into the table.

First of all we need to set the server property to load file data.

USE MASTER GO EXEC sp_configure 'xp_cmdshell', 1; GO RECONFIGURE; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO -- Add 'bulkadmin' to the correct user ALTER SERVER ROLE [bulkadmin] ADD MEMBER [NT AUTHORITY\SYSTEM] GO

Now we are writing the below code

DECLARE @file_nm     VARCHAR(250),         @Sql_Query   NVARCHAR(4000),         @Source_Path VARCHAR(500) ='G:\Books\SSIS',         @file_path   VARCHAR(500),         @cmdstr      NVARCHAR(400),         @n           INT=1,         @cnt         INT,                                 @FileName NVARCHAR(MAX),                                 @ReversedPath NVARCHAR(MAX),         @ExtLength INT DECLARE @files TABLE   (      id      INT IDENTITY,      file_nm VARCHAR(500)   )   SET @cmdstr = 'dir ' + @Source_Path + ' /b';   INSERT INTO @files EXECUTE Xp_cmdshell   @cmdstr   SET @cnt=(SELECT Count(*)           FROM   @files           WHERE  file_nm IS NOT NULL)   SELECT @cnt   WHILE @n <= @cnt   BEGIN       SET @file_nm =(SELECT file_nm                      FROM   @files                      WHERE  id = @n)       SET @file_path= @Source_Path + '\' + @file_nm;                     SET @ReversedPath = REVERSE(@file_path)                   SET @ExtLength = CHARINDEX('.', @ReversedPath)                   SET @FileName = RIGHT(@file_path, CHARINDEX('\', @ReversedPath)-1)                   SET @FileName = LEFT(@FileName, LEN(@FileName) - @ExtLength)         SET @Sql_Query = 'INSERT INTO File_details(File_Nm,File_data) '                        + 'SELECT ' + '''' + @FileName + '''' + ', * '                        + '  FROM Openrowset( Bulk ' + '''' + @file_path                        + '''' + ', Single_Blob) as book'         EXEC (@Sql_Query)         SET @n=@n + 1;   END

Before running this script records in the table.  

Now running this script.  

See the records in the table.   

Data loaded successfully into the table.