Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove comments from the SQL Code Expand / Collapse
Author
Message
Posted Sunday, March 29, 2009 2:51 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
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
Posted Monday, December 27, 2010 5:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 7:35 AM
Points: 1, Visits: 283
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
Posted Saturday, February 12, 2011 4:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, June 22, 2012 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 5:24 AM
Points: 4, Visits: 25
/*

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
Posted Monday, June 25, 2012 2:38 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
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
Posted Thursday, June 28, 2012 4:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
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
Posted Wednesday, July 4, 2012 2:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 10:21 AM
Points: 1,219, Visits: 2,210
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
Posted Wednesday, July 4, 2012 7:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 8:42 AM
Points: 34, Visits: 184
Andrew Watson-478275 (7/4/2012)
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.


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
Posted Wednesday, July 4, 2012 2:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 3:39 PM
Points: 150, Visits: 615
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
Posted Thursday, July 5, 2012 9:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 1, 2013 7:40 AM
Points: 145, Visits: 336
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 »

Add to briefcase

Permissions Expand / Collapse