Andy Mallon has written a T-SQL function to parse file paths from strings:
Writing & reading code is easier if you understand the logic before attacking the code. I find this to be particularly important when you anticipate complicated code. SQL Server sucks at parsing strings, so I anticipate complicated code.
How do you identify the directory from a file path? That’s just everything up to the last slash–and I like to include that final slash to make it clear it’s a directory.
How do you identify the file name from a file path? That’s just everything after the final slash.
The key here is going to be identifying that final slash, and grabbing the text on either side.
Read on for the function.