[mssql] Getting directory path

Following is the function to get the directory path of certain full path.

DECLARE @cPath varchar(100),
        @cDir  varchar(100)

SET @cPath = 'D:\TEMP\test.txt'
SET @cDir = (CASE WHEN charindex('\', @cPath) = 0
               THEN ''
               ELSE left(@cPath, len(@cPath) - charindex('\', reverse(@cPath)))
             END)
SELECT @cDir