SQL Server find number of occurrences for a string

 Scenario:

List Employees with their the roles.

My User
Employee ID	RoleID
1	1
2	2
3	1

Query:

DECLARE @searchString VARCHAR(1000)

SET @searchString = 'string' --Say 1

SELECT ( LEN(CAST([Data] AS VARCHAR(MAX))) - LEN(REPLACE(CAST([Data] AS VARCHAR(MAX)), @searchString, '')) ) / LEN(@searchString) [TotalCount],
       *
FROM   MyTable
ORDER  BY [TotalCount] DESC 

Output:

TotalCount	RoleID
2	1
1	2