Remove comments from the SQL Code

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by shiva challa » Remove comments from the SQL Code

Remove comments from the SQL Code

Rate Topic

Display Mode

Topic Options

Author

Message

shivaram challa

Posted Sunday, March 29, 2009 2:51 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 13, 2012 9:19 AM
Points: 140, Visits: 316

Comments posted to this topic are about the item Remove comments from the SQL Code

Kindest Regards,
Shivaram Challa (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help

Post #685687

Puska

Posted Monday, December 27, 2010 5:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:32 AM
Points: 1, Visits: 233

It's not good.
It doesn't take care if comments are in string (between single quote) when they aren't actually comments.

Post #1039376

michaelreichman

Posted Saturday, February 12, 2011 4:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 14, 2011 9:14 PM
Points: 1, Visits: 2

This doesnt work for me. My version of Sybase only supports two arguments for charindex.

Post #1063053

rajesh_sortur

Posted Friday, June 22, 2012 8:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 04, 2012 11:17 PM
Points: 4, Visits: 14

/*

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

Post #1319979

shivaram challa

Posted Monday, June 25, 2012 2:38 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 13, 2012 9:19 AM
Points: 140, Visits: 316

Hi Rajesh

SELECT * FROM dbo.Ufn_RemoveComments2 (' HELLO  /* WORLD */ ')

This code doesn't seem to yield any results using your function. It does work nicely with nested comments.

Kindest Regards,
Shivaram Challa (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1320884

shivaram challa

Posted Thursday, June 28, 2012 4:32 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 13, 2012 9:19 AM
Points: 140, Visits: 316

You can handle all the nested comments by using the following code:
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

Kindest Regards,
Shivaram Challa (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1322784

Andrew Watson-478275

Posted Wednesday, July 04, 2012 2:51 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:44 AM
Points: 953, Visits: 1,875

I'm curious as to what circumstances you would plan on using this script - the hard part is usually getting developers to include comments in the first place; I can't think of any obvious justification for getting rid of them.

Post #1324865

UKGav

Posted Wednesday, July 04, 2012 7:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:54 AM
Points: 34, Visits: 151

Andrew Watson-478275 (7/4/2012)

Just what I though! I comment nearly everything as it helps yourself as much as others.

Now if there was a script to ADD comments into SQL... Well that'd get 6 out of 5 stars!

~ UKGav

Post #1325012

Dave Vroman

Posted Wednesday, July 04, 2012 2:55 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:13 AM
Points: 46, Visits: 204

We used to do this when the code was placed in an off-site computer. It makes it more difficult for personal who aren't supposed to be in it to mess it up. I haven't done this with SQL, but I have done it with assembler, C, C++ and FORTRAN.

Post #1325130

shivaram challa

Posted Thursday, July 05, 2012 9:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 13, 2012 9:19 AM
Points: 140, Visits: 316

I originally wrote this function, to be used in another function that searches for a keyword in the stored procedures. I was getting false hits for the words in the comments; so this function strips out comments before running the search.

This was back in 2009, I don't remember why exactly I needed the keyword search function though.

Kindest Regards,
Shivaram Challa (http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1325504

« Prev Topic | Next Topic »

Permissions