Or this.

DECLARE @TestStr VARCHAR(20),

@CharToFind CHAR(1);

SET @TestStr = 'This is a string';

SET @CharToFind = ' ';

WITH

e1(n) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), -- 10 rows

e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows

e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows

tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM e4)

SELECT

COUNT(SUBSTRING(@TestStr, n, 1))

FROM

tally

WHERE

SUBSTRING(@TestStr, n, 1) = @CharToFind

AND n <= DATALENGTH(@TestStr);