How to Count Occurrences of String in a SQL Field

 

 

If you are looking to count say how many words are in a SQL field or string, you basically count how many chars were removed after replacing that string with an empty string, and therefore how many instances of it there were.

Below will count the occurrences of a multi-char string by dividing by the length of the string for which is being searched. 

For example:

 

select ((len('countThisStrcountThisStrcountThisStr') - len (replace('countThisStrcountThisStrcountThisStr', 'countThisStr',''))) / len('countThisStr')) as MyStrCount

Results: one row,one column named MyStrCount with a value of 3

replace 'countThisStrcountThisStrcountThisStr' with your column name if you are querying a table and of course add a from clause

Example:

 select

((len([columnName]) - len (replace([columnName], 'countThisStr',''))) / len('countThisStr'))

as MyStrCount from MyTable