I ran across a neat piece of code recently from Gail Shaw. She answered a question on returning the base path from a path in a string. Meaning if I had this string:

c:\Users\Sjones\Documents\text.txt

I’d want to return this:

c:\Users\Sjones\Documents

Her code looked like this, which is a nice, simple, elegant way of finding the path, no matter how many backslashes.

LEFT(@FullPath, LEN(@fullpath) – CHARINDEX(‘\’, REVERSE(@fullpath)))

Of course, you can easily add the last backslash with a slight change to the math.

However I wanted to add some tests. Does this really work? What if I don’t have a backslash? I thought the best way to do this was with a few tSQLt tests, which I quickly built. The entire process was 5-10 minutes, which isn’t a lot longer than if I had been running random tests myself with a variety of strings.

The advantage of tests is that if I come up with a new case, or another potential bug, I copy the test over, change the string and I have a new test, plus all the regressions. I’m not depending on my memory to run the test cases.

I can easily copy this and add new inputs with different paths, and matchout outputs, to test new cases. For example, my first cut produced five tests for these inputs:

c:\myfile.txt

c:\

c:

c:\Documents\myfile.txt

c:\Users\sjones\Documents\myfile.txt

There are certainly other tests, but this 5-10 minutes of work gives me repeatable testing, and if I needed to include this function in a larger project, I already have a series of tests that can be run in my CI process.

What’s more, if I replaced this with a CLR function, such as something with SQL#, I could still use these tests.