Get filename from path string in SQL 2022 | Extract file name in SQL query

This article guide ,

Get filename from path string in SQL 2022 | Extract file name in SQL query

Sometime to performing task we need to get or extract filename from full file path

For. e.g. If we need to restore database from another SQL server backup file. In that case we need to replace or overwrite existing database file. But backup file will have file name different than database file name where we need to restore.

In that case you need to extract the filename from backup file and can be used in restore statement.

Below script can help you to extract exact file name from the full file path or How to Extract Filename from Path using SQL Functions?

Function used to extract filename in SQL query are

LTRIM is a function used in SQL to remove any leading spaces from a string. The function stands for “left trim”, as it trims or removes characters from the left side of the string.

LTRIM(string)

RTRIM is a function used in SQL to remove any trailing spaces from a string. The function stands for “right trim”, as it trims or removes characters from the right side of the string.

RTRIM(string)

REVERSE is a function used in SQL to reverse the order of characters in a string. The function returns a new string with the characters of the original string in reverse order.

REVERSE(string)

SUBSTRING is a function used in SQL to extract a substring from a larger string. The function allows you to specify the starting position of the substring, as well as the number of characters to extract.

SUBSTRING(string, start_position, length)

CHARINDEX is a function used in SQL to find the starting position of a substring within a larger string. The function returns the position of the first occurrence of the substring within the larger string.

CHARINDEX(substring, string, start_position)

Script to Get filename from path string in SQL 2022 or How to Extract Filename from Path using SQL Functions?

Below query help to Parse file name and path from full path.

DECLARE @Datafile varchar(max) = 'C:\Omkar\backups\StackOverflow2013_201809117\StackOverflow2013_1.mdf'
DECLARE @BacpacName varchar(max) = 'C:\Omkar\backups\backup\WideWorldImporters.bacpac'
,@datafilename varchar(max),@bacpac varchar(max)
SELECT @datafilename = LTRIM( RTRIM(REVERSE(SUBSTRING(REVERSE(@Datafile) ,0,CHARINDEX('\', REVERSE(@Datafile),0))))),
@bacpac=LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(@BacpacName), 0,CHARINDEX('\', REVERSE(@BacpacName),0)))))

select @datafilename
SELECT @bacpac

This script can be used to get physical data file name from SQL Server backup file using restore filelistonly command. We need physical file name to restore database with move command in SQL Server.