title: SQL - SUM with SUBSTRING
body: <p>I'm trying to SUM some values from a string but I can't seem to make it work.</p>
  
  <pre><code>SELECT 
      P.NOME
      ,ISNULL(SUM(VPL.QTD),0) as TQTD
      ,CAST(LEFT(SUBSTRING(P.NOME, PATINDEX('%[0-9]%', P.NOME), LEN(P.NOME)),2) AS INT)  
      ,SUM(CAST(LEFT(SUBSTRING(P.NOME, PATINDEX('%[0-9]%', P.NOME), LEN(P.NOME)),2) AS INT)) * ISNULL(SUM(VPL.QTD),0)
  FROM Tabela1 as P
  GROUP BY P.NOME
  </code></pre>
  
  <p>If I comment the SUM I get this:</p>
  
  <p><a href="https://i.stack.imgur.com/FAL0H.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FAL0H.png" alt="enter image description here"></a></p>
  
  <p>But when I try to sum the CAST and the VPL.QTD I get this:</p>
  
  <pre><code>"Conversion failed when converting the varchar value 'Ga' to data type int."
  </code></pre>
  
  <p>Also, this substring <code>SUBSTRING(P.NOME, PATINDEX('%[0-9]%', P.NOME), LEN(P.NOME)</code> returns <code>13 kgs</code></p>
  
  <p>I'm using Microsoft Sql Server</p>