IF OBJECT_ID('dbo.TruncStrOnDeliminator','FN') IS NOT NULL
BEGIN
		print 'Dropping function TruncStrOnDeliminator...'
		DROP FUNCTION dbo.TruncStrOnDeliminator
END
GO
print 'Creating function TruncStrOnDeliminator...'
GO
CREATE FUNCTION dbo.TruncStrOnDeliminator(@InputStr nvarchar(4000),@len INTEGER,@delim CHAR(1) = ',')
RETURNS nvarchar(4000)
AS
BEGIN
    	/********************************************************************************
	Create Date: 5/20/2010 4:19:33 PM
	Author: bgunion
	Purpose: Truncate the string at the last instance of deliminator.
	select dbo.TruncStrOnDeliminator( 'Aggregates, Asphalt Cleaning, Coating, Cracks, Patching, Repairs, Sealing, Striping',60,default)
	********************************************************************************/
  DECLARE @ReturnStr nvarchar(4000), @n INT;
 
	IF @len > len(@InputStr) SET @ReturnStr = @InputStr;
ELSE
	BEGIN
		SET @n = 0
 
		WHILE @ReturnStr IS NULL
		BEGIN
			IF charindex(@delim,@InputStr,@n+1) >= @len 
			BEGIN
				SET @ReturnStr = SUBSTRING(@InputStr,0,@n)
				BREAK
			END
			ELSE
				SELECT @n = charindex(@delim,@InputStr,@n+1)
		END
	END
 
  RETURN @ReturnStr
END
 
GO