title: How to count the occurrences of a string in mysql
body: <p>I have a database view (viewAll) containing these columns:</p>
  
  <p>Locus, varchar(10)<br>
  MismatchDesc, varchar(200)</p>
  
  <p>This table could look like:</p>
  
  <pre><code>Locus MismatchDesc
  HLA-A 435delA;553insG
  HLA-B 665subA;776subA;998delG 
  HLA-B 776subA;998delG
  HLA-B 998delG
  HLA-A 553insG
  </code></pre>
  
  <p>I need to produce a view with additional column <em>MismatchCount</em> So that it looks like:</p>
  
  <pre><code>Locus MismatchDesc MismatchCount
  HLA-A 435delA;553insG 1
  HLA-B 665subA;776subA;998delG 1 
  HLA-B 776subA;998delG 1
  HLA-B 998delG 3
  HLA-A 553insG 2
  </code></pre>
  
  <p>Currently I have:</p>
  
  <pre><code>CREATE VIEW mismatchCount AS SELECT Locus, MismatchDesc, SUM(IF('%'+MismatchDesc+'%',1,0)) AS MismatchCount FROM viewAll GROUP BY MismatchDescGDNA ORDER BY mismatchCount ASC;
  </code></pre>
  
  <p>I had hoped it would produce my required output using sneaky wildcards and the sum function, but it is not, it is simply producing an output in a more complicated way than had I just used 'count'.</p>
  
  <p>So this currently produces output:</p>
  
  <pre><code>Locus MismatchDesc MismatchCount
  HLA-A 435delA;553insG 1 
  HLA-B 665subA;776subA;998delG 1 
  HLA-B 776subA;998delG 1
  HLA-B 998delG 1
  HLA-A 553insG 1 
  </code></pre>
  
  <p>Somewhere out there, there must be a way to count the occurrence of each string, and each instance matching a string?</p>
  
  <p>I cannot separate the strings, which are semi colon separated because I need to count each unique string instance as a whole also. I hope this makes sense.</p>
  
  <p>EDIT</p>
  
  <p>I am not limited to my way of performing the query, if there is a more effective and quicker way, please share, because this is to process a huge amount of data, and therefore likely very slow.</p>