Cool Tips‎ > ‎Data Formatting‎ > ‎

Data Formatting - Enhanced String Trimmer

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Phil Factor
Date:        28 jan 2011

Description

The built-in LTRIM and RTRIM commands in SQL Server are good, but they can't deal effectively or reliably with non-breaking spaces and other control characters.
This function trims of all control characters from the leading and trailing edges of a string (the characters in the set to be trimmed can be configured) 
 

Code

 

IF OBJECT_ID(N'udf_Trim') IS NOT NULL
  
DROP FUNCTION dbo.udf_Trim
GO
CREATE FUNCTION dbo.udf_Trim
/**
summary:   >
This procedure returns a string with all leading and trailing blank space removed. It is similar to the TRIM functions in most current computer languages. You can change the value of the string assigned to @BlankRange, which is then used by the PATINDEX function. The string can be a rangee.g. a-g or a list of characters such as abcdefg.

Author: Phil Factor
Revision: 1.1 changed list of control character to neater range.
date: 28 Jan 2011
example:
     - select dbo.udf_Trim('  678ABC   ')
     - select dbo.udf_Trim('  This has leading and trailing spaces  ')
     - select dbo.udf_Trim('  left-Trim This')
     - select dbo.udf_Trim('Right-Trim This      ')
returns:  
   Input string without trailing or leading blank characters, however these characters are defined in @BlankRange

**/
(@String VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE
      
@BlankRange     CHAR(255),
      
@FirstNonBlank  INT,
      
@LastNonBlank   INT
      
  IF
@String IS NULL
    
RETURN NULL--filter out null strings
    
  -- Here is where you set your definition of what constitutes a blank character.
  -- We've just chosen every 'control' character, the space character and the
  -- non-breaking space. Your requirements could be different!
  
SELECT  @BlankRange = CHAR(0) + '- ' + CHAR(160)
  
  
SELECT  @FirstNonBlank = PATINDEX('%[^' + @BlankRange + ']%', @String)
  
SELECT  @lastNonBlank = 1 + LEN(@String + '|') - (PATINDEX('%[^' + @BlankRange + ']%',
                                                            
REVERSE(@String)))
  
IF @FirstNonBlank > 0
    
RETURN SUBSTRING(@String,@FirstNonBlank, @LastNonBlank-@firstNonBlank)
  
RETURN '' --nothing would be left    
END
GO


ċ
Enhanced trim function.sql
(2k)
Andy Hughes,
Jun 8, 2012, 12:22 PM
Comments