/ Published in: SQL
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)
Example:
select dbo.TruncStrOnDeliminator( 'Aggregates, Asphalt Cleaning, Coating, Cracks, Patching, Repairs, Sealing, Striping',60,default)
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
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