Home Forums SQL Server 7,2000 T-SQL Search for string in stored procedures excluding comments RE: Search for string in stored procedures excluding comments

  • Spinja - Wednesday, March 29, 2017 2:51 PM

    I needed a similar solution. I liked Lowell's clever use of the STUFF function (thanks). The problem I had, as mentioned above, is that multi and single line comments can both override each other.

    -- /* multi-line ignored
    /*
    --*/ this text is not a comment

    My solution was to parse through the file one by one. I kept it as simple as possible. It handles fairly complex nested comments, without a terrible amount of looping and variables. 


    CREATE FUNCTION dbo.RemoveComments (@Value VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
    AS
    BEGIN

        DECLARE @cursor INT = 0 ;
        WHILE PATINDEX('%--%', @Value) > 0 OR    PATINDEX('%/*%', @Value) > 0
        BEGIN
            IF CHARINDEX('--', @Value, 0)BETWEEN 1 AND CHARINDEX('/*', @Value, 0)
                OR    CHARINDEX('/*', @Value, 0) = 0
            BEGIN
                SET @Value = STUFF(@Value, CHARINDEX('--', @Value), ISNULL(NULLIF(CHARINDEX(CHAR(13) + CHAR(10), @Value, CHARINDEX('--', @Value)), 0), LEN(@Value)) - CHARINDEX('--', @Value) + 2, '') ;
            END ;

            ELSE
            BEGIN
                SET @cursor = -1 ;
                WHILE CHARINDEX('/*', @Value, @cursor + 1)BETWEEN 1 AND CHARINDEX('*/', @Value, @cursor + 1)
                SET @cursor = CHARINDEX('/*', @Value, @cursor + 1) ;

                SET @Value = STUFF(@Value, @cursor, CHARINDEX('*/', @Value, @cursor) - @cursor + 2, '') ;
            END ;
        END ;

        RETURN @Value ;

    END ;
    go
    ----------------------------------------------------

    select dbo.removecomments( '
    /*
    /*
    /*
    /*
    remove1
    */*/*/*/
    --remove2 /* remove3
    keep1--*/ remove4
    /* remove5*//* -- remove6 */ keep2
    --/*
    keep3
    -- remove7
    --/*
    keep4/*
    --/*
    remove8
    --*/
    */keep5--*/
    ' )

    Welcome to SSC. You could submit what you posted as a script here and help more people out. 😉

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001