Alternatives · Basic Logics · Data Analysis · Data Management · Real Scenario Logic's · String Manipulation

SuperTrim Function in SQL Server

After Reading my Article – To Remove Multiple and Duplicate Spaces to Single Space in SQL Server, I received the email where one of my friend is asking for the below requirements-

  • To Remove Invisible TAB, Carriage Return, Line Feed characters, White Spaces, Non-Breaking Spaces etc.,
  • Leading and Trailing spaces need to remove.
  • Further, contiguous occurrences of more than one space will be replaced with a single space.

Occasionally, We are trying to remove invisible and white spaces from a string in SQL Server,
But LTrim or RTrim and Trim functions not works? If Yes,then check the below solution.
Below are Some of the, Space look alike culprits and these cannot be removed by using the standard LTrim or RTrim and Trim Functions.

  Char(9)   - Invisible Horizontal Tab 
  Char(10)  - New Line/Line Feed/Line Break 
  Char(11)  - Invisible Vertical Tab 
  Char(12)  - Form Feed
  Char(13)  - Carriage Return 
  Char(14)  - Column Break
  Char(160) - Non-breaking space

Actually, We will use strings for storing data like the name, address, description, review and feedback etc., Rather than all of the time, our data contains the Line breaks, Invisible horizontal and vertical tabs , Carriage Return, Non-Breaking Spaces or Column Breaks in the string. We might need some formatting or handling while dealing with these type of junk data in multiple sources like excel file, CSV file and flat files. While converting Data from Excel files to Tables in SQL Server, these line breaks and all are often an issue in very large files because they are tricky to find and to fix. We will able to see those junk text, if we copy these data in any of the Notepad , Notepad++ or Excel.

We can remove them by following the below 3 steps:

1. Replace the Invisible TAB including both Horizontal and Vertical, Carriage Return, Line Feed/Line Break/New Line characters, White Spaces, Column Break and Non-Breaking Spaces
with Normal trim-able Space.
2. Replace the Double, Multiple and Further contiguous occurrences of already replaced normal spaces(during step 1) will be replaced with a Single Space.
3. Leading and Trailing spaces will be removed as the last step.

In this particular blog, I have posted a simple method for solving the above challenges as per the steps mentioned above. This solution is used in the process of increasing Data Quality, used in some of the Search Algorithms in healthcare data and can also be used in Data Analysis and Data Management processes to confirm the Standard of Data.

SUPER TRIM – SQL SCRIPT

CREATE OR ALTER FUNCTION dbo.SUPERTRIM 
( @String VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS
BEGIN  
    SET @String = 
    LTRIM(RTRIM(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
        REPLACE(
		REPLACE(
		REPLACE(
		REPLACE(
		REPLACE(
        REPLACE(@String,CHAR(10),' ')
		,CHAR(11),' ')
		,CHAR(12),' ')
        ,CHAR(13),' ')
		,CHAR(14),' ')
		,CHAR(160),' ')
        ,CHAR(13)+CHAR(10),' ')
    ,CHAR(9),' ')
    ,' ',CHAR(17)+CHAR(18))
    ,CHAR(18)+CHAR(17),'')
    ,CHAR(17)+CHAR(18),' ')
    ));
 
    RETURN @String;    
END;
SAMPLE DDL SCRIPT AND DATA POPULATION WITH UNIT TEST CASES
--Declaring
DECLARE @Tbl TABLE(col_1 VARCHAR(100));

--Test Samples
INSERT INTO @Tbl (col_1)
VALUES 
('  EY     y			
Salem')
, ('  EY     P    ort       Chennai   ')
, ('  EY     Old           Park   ')
, ('  EY   ')
, ('  EY   ')
,(''),(null),('d    					   
    f');

--Checking Test Samples
SELECT  col_1 AS [Test String],dbo.SUPERTRIM(col_1) AS SUPERTRIM
FROM @Tbl;
COMPARISION SCREENSHOT
SuperTrim-ed Data

CONCLUSION

For reference, there is no System function available in SQL Server for handling the above mentioned Space like junks. So, We use one UDF (user defined functions) named SuperTrim to remove all Invisible TAB, Non-Breaking Spaces, White Spaces, Carriage returns, Line Feed Characters, Leading, Trailing, Multiple and Duplicates Spaces. Here, we Use SuperTrim UDF as the next level implementation of normal Trim Function. . I feel really happy if this article is useful for you !

Thanks for reading!

FOLLOW BLOG for Interesting Upcoming Posts!

Advertisement

4 thoughts on “SuperTrim Function in SQL Server

  1. Consider NOT using the “pattern substitution and replacement method” (the one where spaces are replaced by 3 successive REPLACES). I wrote an article on it’s use a long time ago and someone identified a much quicker method. That method is located at the following URL, which is a part of the discussion associated with the article that I speak of.

    https://www.sqlservercentral.com/forums/reply/1081230

    If you view the posts that follow that, you’ll run into all of the performance testing that was done. It also led to the idea that binary collation should also be used for a nearly extra order of magnitude in performance. The final code for that can be found in the same discussion at the following post…

    https://www.sqlservercentral.com/forums/reply/1081522

    The content of the original article can be found at the following URL.

    https://www.sqlservercentral.com/articles/replace-multiple-spaces-with-one

    If you scroll nearly to the bottom of the article, you find a clickable link titled “” that leads to all of the posts in the discussion for the article.

    Apologies for amount white space between the lines of code in the discussion items. SSC went though change last summer & they managed to screw up all of the legacy code.

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s