Revision: 47493
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at June 9, 2011 03:33 by michanne
Initial Code
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
Initial URL
Initial Description
Function to truncate a string at the last instance of deliminator before exceeding cutoff. Example: select dbo.TruncStrOnDeliminator( 'Aggregates, Asphalt Cleaning, Coating, Cracks, Patching, Repairs, Sealing, Striping',60,default)
Initial Title
Function: TruncStrOnDeliminator
Initial Tags
Initial Language
SQL