title: Error 'Error converting data type varchar to numeric' - Create new table
body: <p>I have a table "Table1_201403" with 1 varchar column 'Column 0'. Now, I want to insert the necessary data from this table to Master_Table2 with the query below -</p>
  
  <pre><code>SELECT '2014/03' AS Period
      ,ltrim(rtrim(SUBSTRING([Column 0], 1, 12))) AS 'Account'
      ,cast(ltrim(rtrim(SUBSTRING([Column 0], 17, 2))) + ltrim(rtrim(SUBSTRING([Column 0], 15, 2))) + ltrim(rtrim(SUBSTRING([Column 0], 13, 2))) AS DATE) AS Trans_Date
      ,CASE 
          WHEN ltrim(rtrim(SUBSTRING([Column 0], 30, 1))) = '-'
              THEN CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))) AS DECIMAL) / 100) AS DECIMAL(9, 2)) * - 1
          ELSE CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))) AS DECIMAL) / 100) AS DECIMAL(9, 2))
          END AS Amount
  INTO Master_Table2
  FROM Table1_201403
  WHERE SUBSTRING([Column 0], 11, 1) &lt;&gt; 'B'
  </code></pre>
  
  <p>When I try to do this, I get the error 'Error converting data type varchar to numeric'.</p>
  
  <p>What can be a suitable solution. Any help will be appreciated.</p>
  
  <p>Thanks.</p>
  
  <p>Modified -</p>
  
  <pre><code>    SELECT '2014/03' AS Period
      ,ltrim(rtrim(SUBSTRING([Column 0], 1, 12))) AS 'GL Account'
      ,cast(ltrim(rtrim(SUBSTRING([Column 0], 17, 2))) + ltrim(rtrim(SUBSTRING([Column 0], 15, 2))) + ltrim(rtrim(SUBSTRING([Column 0], 13, 2))) AS DATE) AS Trans_Date
      ,case
          when ltrim(rtrim(SUBSTRING([Column 0], 30, 1))) = '-'
                                  THEN( 
                                          case when isnumeric(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))))=1 then            
                                          CAST(CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal) / 100) AS decimal(9,2)) AS decimal)*-1 
                                          else 
                                          CAST(CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal) / 100) AS decimal(9,2)) AS varchar)*-1 end
                                      )
                                  ELSE(
  
                                          case when isnumeric(ltrim(rtrim(SUBSTRING([Column 0], 19, 11))))=1 then            
                                          CAST(CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal) / 100) AS decimal(9,2)) AS decimal) 
                                          else 
                                          CAST(CAST((CAST(ltrim(rtrim(SUBSTRING([Column 0],19,11))) AS decimal) / 100) AS decimal(9,2)) AS varchar) end
                                      )
              END  as Amount
              INTO TABLE2
  FROM TABLE1
  WHERE SUBSTRING([Column 0], 11, 1) &lt;&gt; 'B'
  </code></pre>
  
  <p>I tried using the IsNumeric Logic. But could not get the code working. Please have a look.
  @Adi T</p>
  
  <p>And, the data is as below got using your script @t_m -</p>
  
  <p>Column 0    ValidDecimal
  006125      01031400000451745+        JNLCIS Batch 01/03/2014                                                          N        BA0119           C                 L                                                                                                                            1
  006125      01031400000212077+        JNLCIS Batch 01/03/2014                                                          N        BA0120           C                 L                                                                                                                            1
  006125      01031400000311569+        JNLCIS Batch 01/03/2014                                                          N        BA0119           P                 L                                                                                                                            1</p>