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:
Thanks a lot, works perfectly
Post a Comment