2

This question already has an answer here:

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 \"?

marked as duplicate by Andriy M, mustaccio, Michael Green, Marian, Mark Sinkinson Nov 1 '16 at 9:55

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

4

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

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