title: How to cast SQL substring to decimal?
body: <p>I have a varchar column with various strings in table PriceTerm. so some of those I want to copy to another column.</p>
  
  <p>So the source name and format is</p>
  
  <pre><code>[AdditionalDescription] [varchar](255) NOT NULL
  </code></pre>
  
  <p>and destinations</p>
  
  <pre><code>[PercentAddition] [decimal](28, 10) NOT NULL
  </code></pre>
  
  <p>I only want to cast those that contain % picked by SQL:</p>
  
  <pre><code>select AdditionalDescription from PriceTerm where AdditionalDescription like '%[%]%'
  </code></pre>
  
  <p>The output is like this.</p>
  
  <pre><code>AdditionalDescription
  7,5 %
  7,5%
  7,5%
  6 %
  7,5%
  6 %
  3 %
  ....
  </code></pre>
  
  <p>That return 1696475 rows so it is a lot.</p>
  
  <p>Obviously I have to remove % sign before I can cast it to a decimal value.</p>
  
  <p>So I think it is several substeps.</p>
  
  <ol>
  <li>Pick the rows to convert. Done by SQL above.</li>
  <li>Remove  % so only number left in string.</li>
  <li>Convert string to decimal and put it in column PercentAddition.</li>
  </ol>
  
  <p>My question is how to implement step 2 and 3 ?</p>
  
  <p><strong>UPDATE:</strong>
  I have tried those 2 SQL.</p>
  
  <pre><code>SELECT 
  CONVERT(decimal(28,10), LTRIM(RTRIM(REPLACE(REPLACE([ADDITIONALDESCRIPTION], ',', '.'), '%', ''))))
  FROM PriceTerm
  WHERE AdditionalDescription like '%[%]%'
  
  SELECT 
  CAST(LTRIM(RTrim(REPLACE(REPLACE([ADDITIONALDESCRIPTION], ',', '.'), '%', ''))) AS Decimal(28,10))
  FROM PriceTerm
  WHERE AdditionalDescription like '%[%]%'
  </code></pre>
  
  <p>Both returned the same error.</p>
  
  <p>Error converting data type varchar to numeric.</p>
  
  <p>But the following works</p>
  
  <p>SELECT 
  CONVERT(decimal(28,10), LTRIM(RTRIM(REPLACE(REPLACE('5,8%', ',', '.'), '%', ''))))
  FROM PriceTerm
  WHERE AdditionalDescription like '%[%]%'</p>
  
  <p>I try to list those that fulfill the condition and fail the conversion.</p>
  
  <p>SELECT 
  LTRIM(RTRIM(REPLACE(REPLACE([ADDITIONALDESCRIPTION], ',', '.'), '%', ''))) as PercentAddition
  FROM PriceTerm
  WHERE (AdditionalDescription like '%[%]%') AND ISNUMERIC(LTRIM(RTRIM(REPLACE(REPLACE([ADDITIONALDESCRIPTION], ',', '.'), '%', '')))) = 0</p>
  
  <pre><code>PercentAddition
  express + 30
  Express Transport +30
  tillägg till faktura 115423. express +30
  + 30    (1.30*42.4sek=55.12sek)
  + 30    (1.30*42.4sek=55.12sek)
  7.5*
  7.5*
  7..5
  7..5
  7.5.
  7.5.
  1.5  (minimi 4.20 €)
  335 * 25 = 418.75
  335 * 25= 418.75
  10.6 &amp;
  vastaanottajan rahdinmaksu +4
  Expressfrakt!!+30
  </code></pre>
  
  <p>Strange none of those contains % char.</p>
  
  <p><strong>UPDATE 2:</strong>
  I try to filter away data that cannot be converted with this SQL</p>
  
  <pre><code>SELECT AdditionalDescription
  FROM PriceTerm 
  WHERE Created BETWEEN '2004' AND '2005' AND
  AdditionalDescription like '%[%]%' AND
  PercentAddition = 0 AND 
  ISNUMERIC(RTRIM(LTRIM(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.')))) = 0
  </code></pre>
  
  <p>The output:</p>
  
  <pre><code>AdditionalDescription
  
  Snabbleverans + 30 %
  Snabbleverans + 30 %
  455 € + 4%
  1,5 % (minimi 4,20 €)
  1,5 % (minimi 4,20 €)
  bränsle 7,5% ingår.
  </code></pre>
  
  <p>So those strings contain % but it is not possible to convert them to decimals. I want to skip those.</p>
  
  <p>Counting valid numbers</p>
  
  <pre><code>SELECT Count(*)
  FROM PriceTerm 
  WHERE Created BETWEEN '2004' AND '2005' AND
  AdditionalDescription like '%[%]%' AND
  PercentAddition = 0 AND 
  ISNUMERIC(RTRIM(LTRIM(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.')))) = 1
  </code></pre>
  
  <p>80051</p>
  
  <p>Update the PercentAddition column</p>
  
  <pre><code>UPDATE PriceTerm 
  SET PercentAddition = CONVERT(decimal(28,10), RTRIM(LTRIM(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.'))))
  WHERE Created BETWEEN '2004' AND '2005' AND
  AdditionalDescription LIKE '%[%]%' AND
  PercentAddition = 0
  AND ISNUMERIC(RTRIM(LTRIM(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.')))) = 1
  </code></pre>
  
  <p>Result</p>
  
  <p>Error converting data type varchar to numeric.</p>
  
  <p>Anyone that have a hint of the reason ?</p>
  
  <p><strong>UPDATE 3:</strong></p>
  
  <p>So for example all those strange rows above that cannot be parsed is gonne be skiped.</p>
  
  <pre><code>+ 30    (1.30*42.4sek=55.12sek)
  7.5*
  7.5*
  7..5
  </code></pre>
  
  <p>All above is converted to 0. Only those will be converted to decimal:</p>
  
  <pre><code>7,5%  -&gt; 7.5
  7,7 % -&gt; 7.7
  2.5   -&gt; 2.5
  </code></pre>
  
  <p>I also tried to figure out how SQL CASE WHEN works. Could be something useful ?
  Tried this:</p>
  
  <pre><code>UPDATE PriceTerm 
  SET PercentAddition = 
    CASE
      WHEN ISNUMERIC(RTRIM(LTRIM(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.')))) = 1 THEN      
        CONVERT(decimal(28,10), RTRIM(LTRIM(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.'))))
      ELSE 
        0.0
      END 
  WHERE Created BETWEEN '2004' AND '2005' AND
  AdditionalDescription LIKE '%[%]%' AND
  PercentAddition = 0
  AND ISNUMERIC(RTRIM(LTRIM(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.')))) = 1
  </code></pre>
  
  <p>Output still Error converting data type varchar to numeric.
  I even consider a stored procedure, but I have never used that before.
  SQL Server 2012 have Try_Convert that would be really useful in this case. But I don't found a similar alternative for 2008 R2.</p>