0

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!

2

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)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.