I needed a similar solution. I liked Lowell's clever use of the STUFF function (thanks). The problem I had, as mentioned above, is that multi and single line comments can both override each other.
-- /* multi-line ignored
/*
--*/ this text is not a comment
My solution was to parse through the file one by one. I kept it as simple as possible. It handles fairly complex nested comments, without a terrible amount of looping and variables.
CREATE FUNCTION dbo.RemoveComments (@Value VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @cursor INT = 0 ;
WHILE PATINDEX('%--%', @Value) > 0 OR PATINDEX('%/*%', @Value) > 0
BEGIN
IF CHARINDEX('--', @Value, 0)BETWEEN 1 AND CHARINDEX('/*', @Value, 0)
OR CHARINDEX('/*', @Value, 0) = 0
BEGIN
SET @Value = STUFF(@Value, CHARINDEX('--', @Value), ISNULL(NULLIF(CHARINDEX(CHAR(13) + CHAR(10), @Value, CHARINDEX('--', @Value)), 0), LEN(@Value)) - CHARINDEX('--', @Value) + 2, '') ;
END ;
ELSE
BEGIN
SET @cursor = -1 ;
WHILE CHARINDEX('/*', @Value, @cursor + 1)BETWEEN 1 AND CHARINDEX('*/', @Value, @cursor + 1)
SET @cursor = CHARINDEX('/*', @Value, @cursor + 1) ;
SET @Value = STUFF(@Value, @cursor, CHARINDEX('*/', @Value, @cursor) - @cursor + 2, '') ;
END ;
END ;
RETURN @Value ;
END ;
go
----------------------------------------------------
select dbo.removecomments( '
/*
/*
/*
/*
remove1
*/*/*/*/
--remove2 /* remove3
keep1--*/ remove4
/* remove5*//* -- remove6 */ keep2
--/*
keep3
-- remove7
--/*
keep4/*
--/*
remove8
--*/
*/keep5--*/
' )