Posted By

michanne on 06/09/11


Tagged

StringParsing


Versions (?)

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)

  1. IF OBJECT_ID('dbo.TruncStrOnDeliminator','FN') IS NOT NULL
  2. begin
  3. print 'Dropping function TruncStrOnDeliminator...'
  4. DROP FUNCTION dbo.TruncStrOnDeliminator
  5. end
  6. GO
  7. print 'Creating function TruncStrOnDeliminator...'
  8. go
  9. CREATE FUNCTION dbo.TruncStrOnDeliminator(@InputStr nvarchar(4000),@len integer,@delim char(1) = ',')
  10. RETURNS nvarchar(4000)
  11. AS
  12. BEGIN
  13. /********************************************************************************
  14. Create Date: 5/20/2010 4:19:33 PM
  15. Author: bgunion
  16. Purpose: Truncate the string at the last instance of deliminator.
  17. select dbo.TruncStrOnDeliminator( 'Aggregates, Asphalt Cleaning, Coating, Cracks, Patching, Repairs, Sealing, Striping',60,default)
  18. ********************************************************************************/
  19. DECLARE @ReturnStr nvarchar(4000), @n int;
  20.  
  21. IF @len > len(@InputStr) SET @ReturnStr = @InputStr;
  22. ELSE
  23. BEGIN
  24. SET @n = 0
  25.  
  26. WHILE @ReturnStr IS NULL
  27. BEGIN
  28. IF charindex(@delim,@InputStr,@n+1) >= @len
  29. BEGIN
  30. SET @ReturnStr = substring(@InputStr,0,@n)
  31. BREAK
  32. END
  33. ELSE
  34. SELECT @n = charindex(@delim,@InputStr,@n+1)
  35. END
  36. END
  37.  
  38. RETURN @ReturnStr
  39. END
  40.  
  41. GO

Report this snippet  

You need to login to post a comment.