How to split one column value into 2 separate columns in SQL Server?

Question

Select physical_name 
From Sys.database_files

How to split the out put file path into 2 parts?

For example:

Select physical_name 
From Sys.database_files

produces this output:

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

which I'd like to change to:

Col1                                                                   col2
----------------------------------------------------------------------------------
C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA   master.mdf
C:\Program Files\MSSQL\DATA                                            muser.mdf

Answer 1

What you need is a function to extract the file name from a path in tsql. That does not exist, but there is the "reverse trick" to get a lastindexof that will find the last backslash, in connection with the SUBSTRING function. This already has a solution here: https://stackoverflow.com/a/19505918/1132334 and more here: Is there a LastIndexOf in SQL Server?

Answer 2

I personally would not use the scalar functions referenced in the above link because scalar functions slow things down. Inline Table Values functions (itvf) are the way to go. It should not matter much in this case because you're likely not dealing with many rows but, for a high-performing alternative to scalar UDFs have as look at this article: http://www.sqlservercentral.com/articles/T-SQL/91724/.

For sys.database_files you can just do this:

WITH prep AS
(
  SELECT physical_name, li = LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))
  FROM sys.database_files
)
SELECT fpath = SUBSTRING(physical_name,1,li), fname = SUBSTRING(physical_name,li+2,8000)
FROM prep;

Here's an re-usable alternative to scalar udfs for this kind of thing:

-- A high-performing, re-usable itvf to split a string on the last delimiter.
CREATE FUNCTION dbo.itvf_getfilename (@fullname varchar(255), @delimiter char(1))
RETURNS TABLE AS RETURN
SELECT fpath = SUBSTRING(@fullname,1,li),
       fname = SUBSTRING(@fullname,li+2,8000)
FROM (VALUES (LEN(@fullname) - CHARINDEX('\',REVERSE(@fullname)))) prep(li);

Here's a usage example:

DECLARE @fullname varchar(255) = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLSERVER2014X64\MSSQL\DATA AjbTest.mdf';
SELECT fpath, fname FROM dbo.itvf_getfilename (@fullname,'\');

... and, against a table:

SELECT fpath, fname
FROM sys.database_files dbf
CROSS APPLY dbo.itvf_getfilename(dbf.physical_name,'\');

Answer 3

try the following code

Select physical_name AS [FullPath] ,
LEFT(physical_name,LEN(physical_name) - charindex('\',reverse(physical_name),1) + 1) [Directory],
REVERSE(LEFT(REVERSE(physical_name),CHARINDEX('\', REVERSE(physical_name), 1) - 1)) [Filename]
From Sys.database_files