2

I'm trying to extract the file name given a variable file path. Some path examples:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CentralDB.mdf

C:\MSSQL\DATA\CentralDB.mdf

C:\DATA\CentralDB.mdf

Expected Out put should be only the file names, i.e:

CentralDb.mdf CentralDB.ldf

The file path length can vary form location to location.

S3S
  • 24,809
  • 5
  • 26
  • 45
Franklin
  • 895
  • 1
  • 11
  • 19
  • 3
    [How do I ask a **good** question?](http://stackoverflow.com/help/how-to-ask) - your question lacks any kind of context, explanation of what you're trying to do - very unclear, very confusing - you need to improve this ! – marc_s Oct 21 '16 at 15:39
  • It's very unclear....what you want to achieve...you want to create a sql function that receives a path and returns just the mdf filename? – Hackerman Oct 21 '16 at 15:40
  • `DECLARE @t TABLE( val VARCHAR(8000) ); INSERT INTO @t(val)VALUES('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CentralDB.mdf'); INSERT INTO @t(val)VALUES('C:\MSSQL\DATA\CentralDB.mdf'); INSERT INTO @t(val)VALUES('C:\DATA\CentralDB.mdf'); SELECT RIGHT(val,CHARINDEX('\',REVERSE(val))-1) FROM @t;` – TT. Oct 21 '16 at 15:49
  • I think you are looking for this. `Select RIGHT(physical_name,CHARINDEX('\',REVERSE(physical_name))-1) From Sys.database_files` – SS_DBA Oct 21 '16 at 15:49
  • http://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path – ivan_pozdeev Oct 22 '16 at 01:59

1 Answers1

7

You can use the reverse of the string to find the first '\' and then take the RIGHT value of that.

declare @var varchar(4000)

set @var = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CentralDB.mdf'
select RIGHT(@var,CHARINDEX('\',REVERSE(@var))-1)

set @var = 'C:\DATA\CentralDB.mdf'
select RIGHT(@var,CHARINDEX('\',REVERSE(@var))-1)

set @var = 'C:\MSSQL\DATA\CentralDB.mdf'
select RIGHT(@var,CHARINDEX('\',REVERSE(@var))-1)
S3S
  • 24,809
  • 5
  • 26
  • 45