SQL doesn't have a Split function (it has no concept of arrays, so it can't return a collection of values except as a table).
However, it does have a Reverse function:
SELECT LEFT(Location, LEN(Location) - CHARINDEX('\', REVERSE('\' + Location))) AS [Result] from MyTable
You can always create an SQL Function to do that if you want to be readable in queries.