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