title: How to handle mixed datatype values in columns using SQL Server
body: <p>I have a question about SQL Server: how to convert scientific notation value to integer values?</p>
  
  <p>We have some bill numbers that were converted to scientific notation. This means they are in the wrong format for bill numbers and should be converted back to regular decimal or numeric format. </p>
  
  <p>I am encountering difficulty converting just those values that have the scientific notation. </p>
  
  <p>The column is of <code>nvarchar(255)</code> data type but these values need to be manipulated back into <code>numeric</code> or <code>decimal</code> data type.</p>
  
  <pre><code>CREATE TABLE [dbo].[test]
  (
      billno [nvarchar](255) NULL
  ) ON [PRIMARY]
  GO
  
  INSERT INTO [dbo].[test] (billno) VALUES (N'9.2091002309e+012')
  INSERT INTO [dbo].[test] (billno) VALUES (NULL)
  INSERT INTO [dbo].[test] (billno) VALUES (N'4.0206570015e+019')
  INSERT INTO [dbo].[test] (billno) VALUES (N'9.2091002204e+013')
  INSERT INTO [dbo].[test] (billno) VALUES (N'SEALED05/10/2016')
  INSERT INTO [dbo].[test] (billno) VALUES (N'3101123631')
  INSERT INTO [dbo].[test] (billno) VALUES (N'57108')
  INSERT INTO [dbo].[test] (billno) VALUES (N'9.1108000026e+014')
  INSERT INTO [dbo].[test] (billno) VALUES (N'97033429A')
  INSERT INTO [dbo].[test] (billno) VALUES (N'5.0846721402e+017')
  </code></pre>
  
  <p>I have tried like this, but if I run this query:</p>
  
  <pre><code>SELECT 
      CASE 
         WHEN billno like '%E+%'
            THEN CAST(CAST(CAST(billno AS FLOAT) AS numeric) AS NVARCHAR(255))
            ELSE billno 
         END expectedresult,
      billno
  FROM
      test324.dbo.test 
  WHERE
      billno NOT IN ('4.0206570015e+019', '5.0846721402e+017')
  </code></pre>
  
  <p>then this is the results:</p>
  
  <pre><code>expectedresult  |billno
  ----------------+------------------
  9209100230900   |9.2091002309e+012
  92091002204000  |9.2091002204e+013
  SEALED05/10/2016|   SEALED05/10/2016
  3101123631      |3101123631
  57108           |57108
  911080000260000 |9.1108000026e+014
  97033429A       |97033429A
  170668650010000 |1.7066865001e+014
  </code></pre>
  
  <p>If I run this query:</p>
  
  <pre><code>SELECT 
      CASE 
         WHEN billno LIKE '%E+%'
            THEN CAST(CAST(CAST(billno AS FLOAT) AS numeric) AS NVARCHAR(255))
            ELSE billno 
      END test,
      billno
  FROM
      test
  </code></pre>
  
  <p>Then I get an error:</p>
  
  <blockquote>
    <p>Msg 8115, Level 16, State 6, Line 2<br>
    Arithmetic overflow error converting float to data type numeric.</p>
  </blockquote>
  
  <p>I need to get all records related output information. Can you please tell me how to do this in SQL Server?</p>