Count Character or words using T-SQL

Here is a simple way to get a specific character or Words count. How many times character or words is in given a string.

Declare @myvar varchar(max),  @tocount varchar(20)
set @myvar = ‘Hello World, Hello World 5 (sandip and patel) (sql) 3 (developer) 4 and SEO and 4 Dotnet developer’
set @tocount = ‘and’

select (len(@myvar) – len(replace(@myvar,@tocount,”))) / LEN(@tocount)

 

As you see this is quite straightforward, the sql script simply substract the length of the string minus the character searched from the full length of the string, giving as a result the character count. The script counting words is simply counting the space characters.