title: How do I count varchar in a varchar using TSQL
body: <p>What is the best way to count the occurence of a varchar within a varchar.</p>
  
  <p>I rather not loop through a text in order to find certain combinations.</p>
  
  <p>This select only find the first </p>
  
  <pre><code>SELECT CASE WHEN CHARINDEX('!','HOW MANY TIMES IS ! IN THIS TEXT ? THIS IS MY QUESTION !' ) &gt; 0 THEN 1 ELSE 0 END
  </code></pre>
  
  <p>Returns 1</p>
  
  <p>I need a method to find the total number of matches</p>
  
  <pre><code>TABLE DATA
  SEARCHTEXT     LONGTEXT
  !              HOW MANY TIMES IS ! IN THIS TEXT ? THIS IS MY QUESTION !
  HELLO          HELLO HELLO HELLO HELLO HELLO HELLO
  L              HELLO HELLO HELLO HELLO HELLO HELLO
  e              more testdata
  </code></pre>
  
  <p>Expected result</p>
  
  <pre><code>Count SEARCHTEXT  LONGTEST
  2     !           MANY TIMES IS ! IN THIS TEXT ? THIS IS MY QUESTION !
  6     HELLO       HELLO HELLO HELLO HELLO HELLO HELLO
  12    L           HELLO HELLO HELLO HELLO HELLO HELLO
  2     e           more testdata
  </code></pre>
  
  <p>Using mssql server 2005</p>