Quick Tests–Function Returns

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:


I’d want to return this:


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.

  ( @fullpath VARCHAR(4000)
RETURNS varchar(4000)
  RETURN LEFT(@FullPath, LEN(@fullpath) – CHARINDEX(‘\’, REVERSE(@fullpath)))

Here’s my base test:

EXEC tsqlt.NewTestClass ‘StringTests’;
CREATE PROCEDURE [StringTests].[test simple path with one backslash]
— 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’

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.