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
    SET @value = REPLACE(@value,'/*/*','/* /*')
    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(10), @Value, CHARINDEX('--', @Value)), 0), LEN(@Value)) - CHARINDEX('--', @Value) + 1, '') ;
        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( '--test
/*/*/*/*/*/*
remove1
*/*/*/*/*/*/
--remove2 /* remove3
keep1--*/ remove2
/* remove4*//* -- remove5 */ keep2
--/*
keep3
-- remove6
--*/
keep4
--/*
keep5/*
--/*
remove7
--*/
*/keep6--*/
/* */ keep7
/*--remove8 /* remove 9 */ remove 10 */ keep8 --remove11' )