I have a string value in a column in my table. It’s a full path and filename for a .tif image. I needed to get just the filename as a column. I couldn’t use a RIGHT() or a LEFT() or a SUBSTRING() because the root paths change all the time.
What I wanted to do was parse the string for \ and once I find the last one, give me the rest of the string, since at that point I know that I’m traversed all the way into the content folder and now I’m getting the file names.
The SQL:
SELECT FullPathFileName, RIGHT(FullPathFileName, CHARINDEX('', REVERSE(FullPathFileName))-1)
FROM tbl_EnviroToFix
Let’s break down what it does.
REVERSE(FullPathFileName) – This takes the string and as you may have guessed, flips it backward (reverses it). The result looks something like this:
CHARINDEX(”, REVERSE(FullPathFileName)) – This moves from Left to Right until it finds the characters specified, in this case, a single backslash. So, in our example above, that would be the 27th character. Now, remember, this is the FIRST that it found but because we’ve flipped the string backward with REVERSE it’s actually the LAST one that is in the string!
RIGHT(FullPathFileName, CHARINDEX(”, REVERSE(FullPathFileName)) – Now that we know where the last is in the string, we can do a RIGHT() to select only the text from the RIGHT of that character position.
The result:
You may have also noticed the -1 in the REVERSE(FullPathFileName))-1) statement. I want to grab AFTER the occurrence of the \ but not including the \ itself so the -1 just moves me over one character.