I modified your code to make it more useful.

DECLARE @s-2 NVARCHAR(MAX)

DECLARE @result INT

DECLARE @count_me NVARCHAR(MAX)

/*

The string to be searched.

*/

SET @s-2 = '1101001001110101'

/*

The character to be counted.

*/

SET @count_me = '00'

/*

Replace the character to be counted with empty string,

and subtract the lengths.

*/

SET @result = (LEN(@s) - LEN(REPLACE(@s,@count_me,'')) )/LEN(@count_me)

/*

Do something with the result

*/

PRINT @result