Quick T/SQL Tip: Parsing Drive and File Information in SQL Server

Parsing full physical file names for information is one of those tasks a Database Administrator finds they do on a semi-frequent basis; from looking up SQL Agent Job output paths or reviewing database file metadata.  Recently, I found myself updating a SQL Server Reporting Services report which provides SQL Agent Job failure history to the DBA Team.  I was adding functionality that would include the output file for any job hitting the report.  This allows the DBA to troubleshoot the failure details without having to navigate to the job on the instance and pull up the location of the file THEN open the file in notepad.  Single-click on the hyperlink of the output file in the new report and the file opens automatically.

Or at least that was the idea...

What I found out was that if there were spaces in the file name the hyperlink would not get generated.  Therefore there was a need to identify those file names with spaces and convert them to underscores (_).  The other issue was that I wanted to be able to employ that single-click philosophy.  In order to do so I had to convert each file stored as a local path to a UNC.

In order to do these tasks I had to identify what files needed to be touched, which in turn led to the following parsing query I'm sharing with you today:

--===========================================================--USING THE CURRENT DATABASE'S DATABASE_FILES VALUES:--===========================================================SELECT physical_name , LEFT(physical_name, LEN(physical_name) - (CHARINDEX('',REVERSE(physical_name), 1))) AS the_file_path , RIGHT(physical_name, CHARINDEX('',REVERSE(physical_name)) - 1)AS the_file_nameFROM sys.[database_files] 

These are the results when run as a test against the sys.database_files System Catalog View.  The process works against any column storing a full physical file path however as is shown when I put it to actual use against msdb.dbo.sysjobsteps:

--===========================================================--EXAMPLE USING msdb.dbo.sysjobsteps.output_file_name VALUES:--===========================================================SELECT output_file_name , LEFT(output_file_name, LEN(output_file_name) - (CHARINDEX('',REVERSE(output_file_name), 1))) AS the_file_path , RIGHT(output_file_name, CHARINDEX('',REVERSE(output_file_name)) - 1)AS the_file_nameFROM msdb.dbo.sysjobstepsWHERE output_file_name IS NOT NULLORDER BY output_file_name