Query to get the physical file name of the database Im trying to get the physical file name of a database, and when i query sys.database_files, under the physical file name column, you get the whole path (ie. S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf ). How can i dynamically extract just the tempdb.mdf off of that string? I was thinking of using SUBSTRING() + CHARINDEX() but cant figure it out since im new to this. CHARINDEX() only searches for a substring from left to right. Is there a function which searches a particular string from right to left? That way i can just search from the last back slash base from my example. Any input would be very much appreciated. Thanks!
You can try this.

@Stringss is a variable to simulate the field you already obtained

DECLARE @String NVARCHAR(MAX)='S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf'

SELECT REVERSE(SUBSTRING(REVERSE(@String),0,CHARINDEX('\',REVERSE(@String),0)))