title: Return a SUBSTRING with a known Prefix and Suffix Character
body: <p>I have a table that has a Prefix of variable length that always ends with <code>.</code> and a suffix of variable length that always begins with <code>-</code>.  The <code>-</code> can also be found in the string of text that I am looking for, so using REVERSE() is likely the best way to identify the suffix <code>-</code>.</p>
  
  <p>ex: <code>Prefix1.ABC-123-Suffix1</code></p>
  
  <p>I'd like the SUBSTRING() result to look like: <code>ABC-123</code></p>
  
  <p>I was able to create both a <code>SUBSTRING()</code> to remove the Prefix and a <code>SUBSTRING()</code> to remove the suffix, but I'm having a hard time applying both.  The returned text can be variable length.</p>
  
  <p>I'm trying to avoid declaring a variable, but am open to it.  I'd prefer to only use SUBSTRING(), REVERSE(), and CHARINDEX() if possible.  </p>
  
  <p>This is what I have so far:</p>
  
  <pre><code>SELECT [Col1]
         ,SUBSTRING((Col1),CHARINDEX('.', (Col1)) + 1, 999) AS [StrippedPrefix]
         ,REVERSE(SUBSTRING(REVERSE(Col1),CHARINDEX('-', REVERSE(Col1)) + 1, 999)) AS [StrippedSuffix]
  
  --new SUBSTRING() with both Prefix and Suffix stripped
  
  FROM [Table1]
  </code></pre>