Technical Article

Remove comments from the SQL Code

,

Description:

This function takes in a varchar(max) as input and strips out all the comments and RETURNS that string. It replaces comments with a carriage return character; this behavior can be easily changed by find/replacing the char(13) in the code to a single space ' '.

Purpose:

  • Returns text with out any comments in it.
  • Handles commet like "--"
  • Handles comments like /* some text */

/* some text

/* some text */

some more text

*/

Usage Example:

USE tempdb
GO
DECLARE @def varchar(100) = '
hello /* first */ hell0 2 /* second /* third */-second */ --third
world'
PRINT dbo.UtilFn_RemoveComments (@def)
GO

Algorithm Logic:

1. Handling nested /*..*/ type comments 

Start a while look; while loop exits if it can't find anymore /* in the input string:

1a. Find the first ending '*/'

1b. Truncate everything after that

1c. Reverse the string and find the first occurance of '*/' again; since the string is reversed, we;d have to look for */ instead of /*

1d. Calculate the comment length

1e. Replace comment with char(13)

1f.  repeat until you can't find any more /*

2. Handling '--' type comment 

2a. Find starting point of '--' 

2b. Get the entire string length after that

2c. Replace comment with char(13)

CREATE FUNCTION dbo.UtilFn_RemoveComments  (@def VARCHAR(MAX)) RETURNS VARCHAR(MAX)
/* 
Author: Shivaram Challa [http://challa.info]
Purpose: Returns text with out any comments  it.  
Limitations: none 
Update History: 
20090420 SChalla    Created Function       
20120628 SChalla    Changed the code to be able to handle nested commemts as well, taking care of the limitation.
*/ 
AS
BEGIN
DECLARE 
        @comment VARCHAR(100), 
        @endPosition INT, 
        @startPosition INT, 
        @commentLen INT,
        @substrlen INT, 
        @len INT

        WHILE (CHARINDEX('/*',@def)<>0)
        BEGIN
                SET @endPosition  = charindex('*/',@def)
                SET @substrlen=len(substring(@def,1,@endPosition-1))
                SET @startPosition = @substrlen - charINDEX('*/',reverse(substring(@def,1,@endPosition-1)))+1
                SET @commentLen = @endPosition - @startPosition
                SET @comment = substring(@def,@startPosition-1,@commentLen+3 ) 
                SET @def = REPLACE(@def,@comment,CHAR(13))
        END


--* Dealing with --... kind of comments *
        WHILE Patindex('%--%',@def) <> 0
        BEGIN
                SET @startPosition = Patindex('%--%',@def)
                SET @endPosition = Isnull(Charindex(CHAR(13),@def,@startPosition),0)
                SET @len = (@endPosition) - @startPosition

                -- This happens at the end of the code block, 
                --   when the last line is commented code with no CRLF characters
                IF @len <= 0 
                        SET @len = (Len(@def) + 1) - @startPosition

                SET @Comment = Substring(@def,@startPosition,@len)
                SET @def = REPLACE(@def,@comment,CHAR(13))
        END

RETURN @def 
END;

Rate

2.3 (10)

You rated this post out of 5. Change rating

Share

Share

Rate

2.3 (10)

You rated this post out of 5. Change rating