SQL: Parse a String with Patindex and Right
Author: jason
Date: 2014-01-23 14:01:47
Category: Musing

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.


jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009