Monday, January 31, 2011

Removing All Duplicate Tabs, Linefeeds, Returns, and Spaces

The trace tables created by SQL Profiler are nice and all, but it's a pain to search through the TextData column.  The code below uses a brute force method to replace all contiguous whitespace characters with a single space.  This makes it possible to search the TextData column without differences in whitespace getting in the way.

    -- Removing All Duplicate Tabs, Linefeeds, Returns, and Spaces. 

    DECLARE @RowsAffected INT = 0
    SET NOCOUNT ON
    
    -- Create all 16 (4^2) Tab, Linefeed, Return, and Space combinations.
    DECLARE @PairTT VARCHAR(2) = CHAR( 9) + CHAR( 9)    
    DECLARE @PairTL VARCHAR(2) = CHAR( 9) + CHAR(10)    
    DECLARE @PairTR VARCHAR(2) = CHAR( 9) + CHAR(13)    
    DECLARE @PairTS VARCHAR(2) = CHAR( 9) + CHAR(32)    
    DECLARE @PairLT VARCHAR(2) = CHAR(10) + CHAR( 9)    
    DECLARE @PairLL VARCHAR(2) = CHAR(10) + CHAR(10)    
    DECLARE @PairLR VARCHAR(2) = CHAR(10) + CHAR(13)    
    DECLARE @PairLS VARCHAR(2) = CHAR(10) + CHAR(32)    
    DECLARE @PairRT VARCHAR(2) = CHAR(13) + CHAR( 9)
    DECLARE @PairRL VARCHAR(2) = CHAR(13) + CHAR(10)
    DECLARE @PairRR VARCHAR(2) = CHAR(13) + CHAR(13)
    DECLARE @PairRS VARCHAR(2) = CHAR(13) + CHAR(32)
    DECLARE @PairST VARCHAR(2) = CHAR(32) + CHAR( 9)
    DECLARE @PairSL VARCHAR(2) = CHAR(32) + CHAR(10)
    DECLARE @PairSR VARCHAR(2) = CHAR(32) + CHAR(13)
    DECLARE @PairSS VARCHAR(2) = CHAR(32) + CHAR(32)
                                                    
    WHILE 1=1
    BEGIN
        SELECT @RowsAffected = COUNT(*) 
          FROM dbo.TraceTableReportPermanent
         WHERE TextData LIKE '%' + @PairTT + '%'
            OR TextData LIKE '%' + @PairTL + '%'
            OR TextData LIKE '%' + @PairTR + '%'
            OR TextData LIKE '%' + @PairTS + '%'
            OR TextData LIKE '%' + @PairLT + '%'
            OR TextData LIKE '%' + @PairLL + '%'
            OR TextData LIKE '%' + @PairLR + '%'
            OR TextData LIKE '%' + @PairLS + '%'
            OR TextData LIKE '%' + @PairRT + '%'
            OR TextData LIKE '%' + @PairRL + '%'
            OR TextData LIKE '%' + @PairRR + '%'
            OR TextData LIKE '%' + @PairRS + '%'
            OR TextData LIKE '%' + @PairST + '%'
            OR TextData LIKE '%' + @PairSL + '%'
            OR TextData LIKE '%' + @PairSR + '%'
            OR TextData LIKE '%' + @PairSS + '%'

        IF @RowsAffected > 0
            UPDATE dbo.TraceTableReportPermanent
               SET TextData = REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(
                              REPLACE(TextData, @PairTT, CHAR(32)),
                                                @PairTL, CHAR(32)), 
                                                @PairTR, CHAR(32)), 
                                                @PairTS, CHAR(32)),
                                                @PairLT, CHAR(32)),
                                                @PairLL, CHAR(32)),
                                                @PairLR, CHAR(32)),
                                                @PairLS, CHAR(32)),
                                                @PairRT, CHAR(32)),
                                                @PairRL, CHAR(32)),
                                                @PairRR, CHAR(32)),
                                                @PairRS, CHAR(32)),
                                                @PairST, CHAR(32)),
                                                @PairSL, CHAR(32)),
                                                @PairSR, CHAR(32)),
                                                @PairSS, CHAR(32))
             WHERE TextData LIKE '%' + @PairTT + '%'
                OR TextData LIKE '%' + @PairTL + '%'
                OR TextData LIKE '%' + @PairTR + '%'
                OR TextData LIKE '%' + @PairTS + '%'
                OR TextData LIKE '%' + @PairLT + '%'
                OR TextData LIKE '%' + @PairLL + '%'
                OR TextData LIKE '%' + @PairLR + '%'
                OR TextData LIKE '%' + @PairLS + '%'
                OR TextData LIKE '%' + @PairRT + '%'
                OR TextData LIKE '%' + @PairRL + '%'
                OR TextData LIKE '%' + @PairRR + '%'
                OR TextData LIKE '%' + @PairRS + '%'
                OR TextData LIKE '%' + @PairST + '%'
                OR TextData LIKE '%' + @PairSL + '%'
                OR TextData LIKE '%' + @PairSR + '%'
                OR TextData LIKE '%' + @PairSS + '%'
        ELSE
            BREAK
             
        RAISERROR('Deleted %d whitespace pairs from dbo.TraceTable',
                   10, 1, @RowsAffected) WITH NOWAIT
    END