title: How to get the middle word using Substring via Charindex of Second Position?
body: <p>Basically what I am trying to do is that I want to get the middle word, using the second occurrence of the same character <strong>(on this case, dash "-")</strong>.</p>
  
  <p>This is the sample input:</p>
  
  <pre><code>declare @word nvarchar(max)
  set @word = 'Technical Materials - Conversion - Team Dashboard'
  </code></pre>
  
  <p>There are three parts on this sentence, and they are divided by '-' dash line.</p>
  
  <p>The <strong>first part</strong> is 'Technical Materials' which I am able to get using:</p>
  
  <pre><code>SELECT LTRIM(RTRIM(SUBSTRING(@word, 0, CHARINDEX('-', @word, 0)))) 
  </code></pre>
  
  <p>The <strong>last set</strong> was 'Team Dashboard' which I am able to get using:</p>
  
  <pre><code>SELECT CASE WHEN LEN(@word) - LEN(REPLACE(@word, '-', '')) = 1
              THEN NULL
          ELSE
              RIGHT(@word,CHARINDEX('-', REVERSE(@word))-1)
          END
  </code></pre>
  
  <p>The problem was, I am having a hard time getting the middle words which is 'Conversion' in this example.</p>