Number of times a particular character appears in a string

You can do that using replace and len.

Count number of x characters in str:

len(str) - len(replace(str, 'x', ''))

There's no direct function for this, but you can do it with a replace:

declare @myvar varchar(20)
set @myvar = 'Hello World'

select len(@myvar) - len(replace(@myvar,'o',''))

Basically this tells you how many chars were removed, and therefore how many instances of it there were.

Extra:

The above can be extended to count the occurences of a multi-char string by dividing by the length of the string being searched for. For example:

declare @myvar varchar(max), @tocount varchar(20)
set @myvar = 'Hello World, Hello World'
set @tocount = 'lo'

select (len(@myvar) - len(replace(@myvar,@tocount,''))) / LEN(@tocount)

Look at the length of the string after replacing the sequence

declare @s varchar(10) = 'aabaacaa'
select len(@s) - len(replace(@s, 'a', ''))
>>6