Thanks for the reply, Bruce. I've written a couple of functions to deal with the problem.
Firstly, the tab path formatting function:
-- =============================================
-- Author: Stefan Benjamin
-- Create date: 02/11/2009
-- Description: Create a formatted TabPath for records
-- =============================================
ALTER FUNCTION [dbo].[formTabPath]
(
-- Add the parameters for the function here
@TabId int
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @TabPath varchar(max) = ''
DECLARE @Level int = (SELECT [Level] FROM dbo.TabsCopy WHERE TabId = @TabId)
DECLARE @TabName varchar(max)
DECLARE @ParentId int
-- Add the T-SQL statements to compute the return value here
WHILE @Level >= 0
BEGIN
SET @TabName = (SELECT TabName FROM dbo.TabsCopy WHERE TabId = @TabId)
SET @ParentId = (SELECT ParentId FROM dbo.TabsCopy WHERE TabId = @TabId)
SET @TabPath = dbo.StripPunctuation(@TabName) + @TabPath
SET @TabId = @ParentId
SET @Level = @Level - 1
END
-- Return the result of the function
RETURN @TabPath
END
Secondly, my function to strip punctuation from tab titles, change spaces to dashes and add the "//" before each level:
-- =============================================
-- Author: Stefan Benjamin
-- Create date: 02/11/2009
-- Description: Strips punctuation from string and replaces spaces with dashes
-- =============================================
ALTER FUNCTION [dbo].[StripPunctuation]
(
-- Add the parameters for the function here
@TabName varchar(max)
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @LevelName varchar(max)
-- Add the T-SQL statements to compute the return value here
SELECT @LevelName = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@TabName,',',''),'.',''),'''' ,''),'!', ''),'&', 'and'),' ', '-')
-- Return the result of the function
RETURN '//' + @LevelName
END
Hope this helps people :)