/*
No Limitations
Please refer mentioned below function to remove comments in String.
*/
CREATE FUNCTION [dbo].[Ufn_Remove_Comments] (@definition VARCHAR(MAX))
RETURNS @TblDefinition TABLE
(
Definition VARCHAR(MAX)
)
AS
BEGIN
/*
Author: Rajesha Soratoor
Purpose: Returns text with out any comments in it.
Description : This code handle nested comments like Mentioned below.
/*
text
/*
text
/*
text
*/
*/
text
*/
--Rajesha soratoor
--soratoor
*/
DECLARE @startPosition INT = 0
DECLARE @endPosition INT = 0
DECLARE @PrevEndPosition INT = 0
DECLARE @comment VARCHAR(MAX) = ''
DECLARE @len INT = 0
DECLARE @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
/*Dealing with /* ... */ kind of comments */
WHILE Patindex('%/*%',@definition) <> 0
BEGIN
SET @startPosition = Patindex('%/*%',@definition)
SET @endPosition = Patindex('%*/%',@definition)
SET @len = (@endPosition + 2) - @startPosition
SELECT @definition = STUFF(@definition,
@startPosition,
@endPosition - @startPosition + 2, --2 is the length of the search term
'')
SET @PrevEndPosition = @startPosition
SET @startPosition = Patindex('%/*%',@definition)
SET @endPosition = Patindex('%*/%',@definition)
WHILE @startPosition > @endPosition OR @startPosition = 0
BEGIN
SELECT @definition = STUFF(@definition,
@PrevEndPosition,
@endPosition - @PrevEndPosition + 2, --2 is the length of the search term
'')
SET @startPosition = Patindex('%/*%',@definition)
SET @endPosition = Patindex('%*/%',@definition)
IF @startPosition = 0 and @endPosition = 0
BREAK
END
END
/*Dealing with --... kind of comments */
SELECT @startPosition = CHARINDEX('--',@definition)
WHILE @startPosition > 0
AND CHARINDEX(@vbCrLf,@definition,@startPosition) > @startPosition
SELECT @definition = STUFF(@definition,
@startPosition,
CHARINDEX(@vbCrLf,@definition,@startPosition) - @startPosition + 2,
''
)
INSERT INTO @TblDefinition
SELECT @definition
RETURN
END
GO