Nicking ColdCoffee's setup:

-- Declare a temp table to hold the file names inside a folder

DECLARE @FolderAndFiles TABLE

(

Sl_Number INT IDENTITY(1,1) ,

FileNameWithFolder VARCHAR(1024)

)

-- Insert data into the table

INSERT INTO @FolderAndFiles (FileNameWithFolder)

SELECT 'C:\Development\Source_Files\VFP9Apps\Trunk' UNION ALL

SELECT 'C:\My Documents\VFPDevelopment' UNION ALL

SELECT 'C:\Program Files\Microsoft SQL Server' UNION ALL

SELECT 'C:\Program Files\Microsoft SQL Server Compact Edition\v3.5'

SELECT Sl_Number, FileNameWithFolder, LEFT(FileNameWithFolder, n)

FROM (

SELECT Sl_Number, FileNameWithFolder, n.n,

nthPos = ROW_NUMBER() OVER(PARTITION BY Sl_Number ORDER BY n.n)

FROM @FolderAndFiles

INNER JOIN (SELECT n = ROW_NUMBER() OVER (ORDER BY NAME) FROM dbo.SYSCOLUMNS) n

ON SUBSTRING(FileNameWithFolder, n.n, 1) = '\'

) d WHERE nthPos = 2