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 1END
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
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
Reference:http://www.exacthelp.com/2015/06/how-to-remove-comment-from-sql-script.html
