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 first put the code in a function, which makes it easier to test.
CREATE FUNCTION GetParentPath
( @fullpath VARCHAR(4000)
)
RETURNS varchar(4000)
AS
BEGIN
RETURN LEFT(@FullPath, LEN(@fullpath) – CHARINDEX(‘\’, REVERSE(@fullpath)))
END
Here’s my base test:
EXEC tsqlt.NewTestClass ‘StringTests’;
go
CREATE PROCEDURE [StringTests].[test simple path with one backslash]
AS
BEGIN
— Assemble
DECLARE @input VARCHAR(4000) = ‘c:\myfile.txt’
, @expected VARCHAR(4000) = ‘c:’
, @actual VARCHAR(4000)
— Assert
EXEC @actual = dbo.GetParentPath
@fullpath = @input
— Assert
EXEC tsqlt.AssertEquals
@Expected = @expected
, @Actual = @actual
, @Message = N’Incorrect Path’
END
GO
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.
