And you probably want to replace CR+LF with a space at the very beginning, as "SET NOCOUNT ON" for SQL Server is the same as

SET

NOCOUNT

ON

CREATE TABLE #Sample(

Sometext varchar(5000)

);

INSERT INTO #Sample

VALUES

('CREATE PROCEDURE Proc1 AS SELECT * FROM sys.tables'),

('CREATE PROCEDURE Proc2 AS

SET

NOCOUNT

ON

SELECT * FROM sys.tables'),

('CREATE PROCEDURE Proc2 AS SET NOCOUNT ON SELECT * FROM sys.tables'),

('CREATE PROCEDURE Proc3 AS SET ' + replicate (' ', 1800) + ' NOCOUNT ON SELECT * FROM sys.tables');

DECLARE @ReplaceChar NCHAR(1)

SET @ReplaceChar = CHAR(7)

SELECT s.Sometext, REPLACE(REPLACE(REPLACE(REPLACE(s.Sometext, CHAR(13)+CHAR(10), ' '), ' ', ' ' + @ReplaceChar), @ReplaceChar + ' ', ''), @ReplaceChar, '')

FROM #Sample s

WHERE REPLACE(

REPLACE(

REPLACE(

REPLACE(s.Sometext, CHAR(13)+CHAR(10), ' '

), ' ', ' ' + @ReplaceChar

), @ReplaceChar + ' ', ''

), @ReplaceChar, ''

) LIKE '%SET NOCOUNT ON%'