DECLARE
  @Text       NVarChar(MAX), -- Где ищем
  @SubString  NVarChar(4000), -- Что ищем
  @Pos        Int = 1 -- С какого символа начинаем

SET @Text = '\
/*CREATE PROCEDURE P1*/
--CREATE PROCEDURE P2
CREATE PROCEDURE P4 AS RETURN;
GO
'

SET @SubString = 'CREATE'

  DECLARE
    @TextLen      Int = LEN(@Text),
    @SubStringLen Int = LEN(@SubString),

    @C1Begin      NVarChar(2) = N'/*',
    @C1End        NVarChar(2) = N'*/',

    @C2Begin      NVarChar(2) = N'--',
    @C2End        NVarChar(2) = NChar(13),

    @C1BeginI     Int,
    @C2BeginI     Int,
    @C1EndI       Int,
    @SubStringI   Int,
    
    @C1Count      Int = 0


  WHILE @Pos > 0 AND @Pos <= @TextLen
  BEGIN

    SET @C1BeginI   = CHARINDEX(@C1Begin, @Text, @Pos)
    SET @C2BeginI   = CHARINDEX(@C2Begin, @Text, @Pos)
    SET @C1EndI     = CHARINDEX(@C1End, @Text, @Pos)
    SET @SubStringI = CHARINDEX(@SubString, @Text, @Pos) 

    PRINT 'I          =' + CAST(@Pos AS NVARCHAR)
    PRINT '@C1Count   =' + CAST(@C1Count AS NVARCHAR(60))
    PRINT 'C1BeginI   =' + CAST(CHARINDEX(@C1Begin, @Text, @Pos) AS NVARCHAR(60))
    PRINT 'C2BeginI   =' + CAST(CHARINDEX(@C2Begin, @Text, @Pos) AS NVARCHAR(60))
    PRINT 'C1EndI     =' + CAST(CHARINDEX(@C1End, @Text, @Pos) AS NVARCHAR(60))
    PRINT 'SubStringI =' + CAST(CHARINDEX(@SubString, @Text, @Pos)  AS NVARCHAR(60))

    IF @C1Count = 0
       AND
       (
        @SubStringI <= 0
        OR
        (@SubStringI < @C1BeginI OR @C1BeginI = 0)
        AND
        (@SubStringI < @C2BeginI OR @C2BeginI = 0)
        AND
        (@SubStringI < @C1EndI OR @C1EndI = 0)
       )
    BEGIN
      PRINT 'Либо строку не нашли вообще, либо нашли и она не закомментирована'
      -- Либо строку не нашли вообще, либо нашли до комментариев
      SET @Pos = @SubStringI;
      BREAK;
    END

    -- Строка есть где-то за комментариями, начинаем колдовать

    -- Начался комментарий C1
    IF @C1BeginI > 0 AND @C1BeginI < @SubStringI AND (@C1BeginI < @C2BeginI OR @C2BeginI = 0)
    BEGIN
      PRINT '/*'

      SET @C1Count += 1;

      SET @Pos = @C1BeginI + LEN(@C1Begin);
      CONTINUE;
    END

    -- Закончился комментарий C1
    IF @C1EndI > 0 AND (@C1EndI < @C2BeginI OR @C2BeginI = 0 OR @C1Count > 0)
    BEGIN
      PRINT '*/'
      IF @C1Count > 0
        SET @C1Count -= 1;

      SET @Pos = @C1EndI + LEN(@C1End);
      CONTINUE;
    END

    -- Начался комментарий C2
    IF @C2BeginI > 0 AND @C2BeginI < @SubStringI AND (@C2BeginI < @C1BeginI OR @C1BeginI = 0) AND @C1Count = 0
    BEGIN
      PRINT '--'
      SET @Pos = CHARINDEX(@C2End, @Text, @C2BeginI + LEN(@C2Begin)); -- Перейти сразу в конец!
      IF @Pos > 0
        CONTINUE;
    END    

    SET @Pos = 0;
    BREAK;
  END

  IF @TextLen = 0
    SET @Pos = 0;

  SELECT @Pos, SUBSTRING(@Text, @Pos, 19);