Extract Database Name From String [duplicate]

Question

In a database we have the full file path for a database field type is nvarchar(255) and structure/data would look like this:

M:\Blue\Red\Green\Folder\Pink\Purple\Database1.accdb
M:\Blue\Red\Orange\Folder1\Folder2\Folder3\Size\Small\Large\DbTest1232323Test.accdb

My question is, how can I extract only the database name – pull only this out:

• Database1.accdb
• DbTest1232323Test.accdb

Since the name of the database can vary, I can't use just a RIGHT() function.

Is there a way I can somehow use a wildcard in conjunction with RIGHT() to say, "Pull everything to the right of the last \"?

Answer 1

This ought to do it. You'll want the last column (FileName_Only).

SELECT f.physical_name,
    REVERSE(f.physical_name) AS physical_name_Reversed,
    CHARINDEX('\', REVERSE(f.physical_name)) AS First_Backslash_When_Reversed,
    LEFT(REVERSE(f.physical_name), CHARINDEX('\', REVERSE(f.physical_name)) - 1) AS FileName_Only_Reversed,
    REVERSE(LEFT(REVERSE(f.physical_name), CHARINDEX('\', REVERSE(f.physical_name)) - 1)) AS FileName_Only
FROM sys.database_files f

Here's another option. The code is a bit leaner:

SELECT 
    f.physical_name,
    REVERSE(f.physical_name) AS physical_name_Reversed,
    CHARINDEX('\', REVERSE(f.physical_name)) AS First_Backslash_When_Reversed,
    RIGHT(f.physical_name, CHARINDEX('\', REVERSE(f.physical_name)) - 1) AS FileName_Only
FROM sys.database_files f