keith-710920 (9/20/2010)
Perhaps I'm missing something here, but no where in Dave's original query did he mention anything about parsing folder structures, parsing multiple strings or anything like that. He simply asked how to take a single string which he already has (presumably from some other code he already knows how to write) and pull out a particular substring.
You are absolutely right Keith, at least about the latter part. ColdCoffee & I posted our column-centric solutions because - well, because that's what is usually asked for. Both solutions will work with a string variable with a little simple modification.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden