Posted By

michanne on 06/09/11

Tagged

Versions (?)

Last Edited at 06/09/11 03:33am

Statistics

Viewed 138 times

Favorited by 0 user(s)

Related snippets

Function: TruncStrOnDeliminator

/ 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)

Highlighted TextExpand

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

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.