T-sql substring

Question

I basically have one column that needs to be tweaked. The format of the column is as follows:

Column A -------- Root\FOLDERPATH\somesubfolderpath\somedocument.doc 

The first line of "Root \" will always be the same length with the same characters. Everything after “Root” may vary depending on the folder structure of the document.

What I need to get is the second line. Using the above example, the column should look like lilke:

 Column A -------- FOLDERPATH 

So, I need to cut "Root", get the line after that and cut everything after that.

Answer 1

One of the methods:

 substring(fld, 6, charindex('\', substring(fld + '\', 6, len(fld))) -1) 

Answer 2

 declare @val varchar(100) set @val = 'Root\FOLDERPATH\somesubfolderpath\somedocument.doc' select left(right(@val, len(@val) - 5), charindex('\', right(@val, len(@val) - 5)) - 1)