Master Station Log
techno-rants, musings, misc geek writing, and occasional caffeine binges
SQL: Parse a String with Patindex and Right
Date: 2014-01-23 14:01:47
Getting a string like a File Name out of another string like a File Path doesn't need to be difficult. I use functions like Pattern Index (PATINDEX), REVERSE, and RIGHT to parse out what I need. According the Technet: Pattern Index returns the starting position of the first occurrence of a pattern.
DECLARE @FilePath AS VARCHAR(MAX) = 'C:\temp\myFolder\myfile.txt';
SELECT RIGHT(@FilePath, PATINDEX('%\%',REVERSE(@FilePath)) -1);
First I REVERSE the String. Look for the Slash Position with PATINDEX. Then I use the RIGHT function to move those number of places. I also subtract One from the Slash position, to remove the slash from the final output.