Jun 11, 2015

How to remove comment from SQL script \ procedures SQL server

Sometimes there is a need to remove or delete comment part of SQL script from procedures, functions etc in SQL server. We can do this using below SQL function:


CREATE FUNCTION [dbo].[RemoveComment](
      @ScriptText AS VARCHAR(MAX)
)

RETURNS VARCHAR(MAX)
AS
BEGIN

      DECLARE @PairChar VARCHAR(2)
      DECLARE @OldPairChar VARCHAR(2)
      DECLARE @Counter AS INT = 1
      DECLARE @StartIndex INT
      DECLARE @EndIndex INT
      DECLARE @FlagSingleQuote BIT
      DECLARE @FlagCommentType BIT
      DECLARE @CommentCount INT
    
      SET @FlagSingleQuote = 0
      SET @FlagCommentType = 0
      SET @CommentCount = 0
      SET @PairChar = SUBSTRING(@ScriptText,1,2)
    
      WHILE  LEN(@ScriptText) > @Counter BEGIN
    
            IF RIGHT(@PairChar,1) = '''' AND @StartIndex IS NULL
                  SET @FlagSingleQuote = CASE WHEN @FlagSingleQuote = 1 THEN 0 ELSE 1 END
          
            IF @FlagSingleQuote = 0 BEGIN
          
                
                  IF  @PairChar = '/*' AND @FlagCommentType = 0 BEGIN
                
                        IF @CommentCount = 0
                              SET @StartIndex = @Counter
                            
                        SET @CommentCount = @CommentCount + 1
                      
                  END ELSE IF @StartIndex IS NOT NULL  AND @FlagCommentType = 0 AND @PairChar = '*/'  BEGIN
                
                        SET @CommentCount = @CommentCount - 1
                      
                        IF @CommentCount = 0
                              SET @EndIndex = @Counter
                            
                  END ELSE IF @StartIndex IS NULL AND @PairChar = '--' BEGIN
                        SET @StartIndex = @Counter
                        SET @FlagCommentType = 1
                  END ELSE IF @FlagCommentType = 1 AND @PairChar = CHAR(13) + CHAR(10)
                        SET @EndIndex = @Counter
                      
                  IF @StartIndex IS NOT NULL AND @EndIndex IS NOT NULL BEGIN
                        SET @ScriptText = STUFF(@ScriptText,@StartIndex,2 + @EndIndex-@StartIndex,'')
                        SET @Counter = @StartIndex - 1
                        SET @FlagCommentType = 0
                  END
                      
                  IF @EndIndex IS NOT NULL BEGIN
                        SET @StartIndex = NULL
                        SET @EndIndex = NULL
                  END
            END
                
            SET @OldPairChar =      @PairChar
            SET @Counter = @Counter + 1
            SET @PairChar = SUBSTRING(@ScriptText,@Counter,2)
    
            IF  (@OldPairChar = '/*' AND @PairChar = '*/') OR (@OldPairChar = '*/' AND @PairChar = '/*' AND @StartIndex IS NOT NULL) BEGIN
                
                  SET @PairChar = ''
                
            END
      END
    
      IF @StartIndex IS NOT NULL AND @EndIndex IS NULL BEGIN
            SET @ScriptText = STUFF(@ScriptText,@StartIndex,LEN(@ScriptText),'')
      END
    
      RETURN @ScriptText
    
END 

For example:
SELECT [dbo].[RemoveComment]('/* SQL SERVER */  Exact help')


Output:  Exact help

1 comment: