SED on SQL Query results-CodePudding

I need to format the results of a query so that it displays the correct information.

I have a table "file name"

id | file name
-------------------
1 | c:\windows\system\system32\user.txt
2 | c:\user\user\arc\eula.txt
3 | z:\networkshares\this\is\where\stuff\goes
4 | z:\servercfgs\pythoncode\libs\convertchar2rsa\c2r.py
5 | serverbackups

The query would be:

select *
from [file name]

The results from the query however, I need to be:

select [file_name] as 'root_folder'
from [file name]

id | root_folder
-----------------------
1 | windows
2 | user
3 | networkshares
4 | servercfgs
5 | serverbackups
------------------------

CodePudding user response：

Since we know the first part will always be <drive letter>:\ or nothing, we can substring to position 4 to start our string. Then, we just need to get the index of the second , using CHARINDEX, and remove 4 from that to get the length we need:

select id
    , case
        when [file name] like '%\%' then SUBSTRING([file name], 4, CHARINDEX('\', [file name], 4) - 4)
        else [file name]
    end root_folder
from tbl

We use the case statement to bypass anything that doesn't have a drive letter.