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