Query to get the physical file name of the database
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)))