March 29, 2009 at 2:51 am
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[/url]
December 27, 2010 at 5:45 am
It's not good.
It doesn't take care if comments are in string (between single quote) when they aren't actually comments.
February 12, 2011 at 4:34 pm
This doesnt work for me. My version of Sybase only supports two arguments for charindex.
June 22, 2012 at 8:29 am
/*
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
June 25, 2012 at 2:38 pm
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[/url]
June 28, 2012 at 4:32 pm
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[/url]
July 4, 2012 at 2:51 am
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.
July 4, 2012 at 7:39 am
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
July 4, 2012 at 2:55 pm
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.
July 5, 2012 at 9:28 am
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[/url]
May 10, 2016 at 9:17 am
Thanks for the script.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy