<div>
Instead of doing the cast like this, I would recommend to use <a href="http://technet.microsoft.com/en-us/library/ms189527.aspx" rel="ugc">STR </a>function:<br />

<pre><code id="code-20-39802098-1">CASE
      WHEN COLUMN like '%E-%' THEN LTRIM(RTRIM(STR(CAST(COLUMN AS FLOAT), 18, 8)))
      WHEN COLUMN like '%E+%' THEN NULL
      ELSE COLUMN
END</code></pre>
</div>


Instead of doing the cast like this, I would recommend to use STR [http://technet.microsoft.com/en-us/library/ms189527.aspx]function:


CASE
      WHEN COLUMN like '%E-%' THEN LTRIM(RTRIM(STR(CAST(COLUMN AS FLOAT), 18, 8)))
      WHEN COLUMN like '%E+%' THEN NULL
      ELSE COLUMN
END

<div>
Why are you NULLing out &quot;E+&quot; values?<br />
<br />
Why not just something like:<br />
<br />
CASE <br />
&nbsp; &nbsp; &nbsp; WHEN COLUMN like '%E%' THEN CAST(CAST(COLUMN AS FLOAT) AS DECIMAL(18,18))<br />
&nbsp; &nbsp; &nbsp; ELSE COLUMN<br />
END
</div>


Why are you NULLing out "E+" values?

Why not just something like:

CASE
      WHEN COLUMN like '%E%' THEN CAST(CAST(COLUMN AS FLOAT) AS DECIMAL(18,18))
      ELSE COLUMN
END

<div>
Also, the data is already in the table as a varchar and needs to be converted to a decimal data type.
</div>


Also, the data is already in the table as a varchar and needs to be converted to a decimal data type.

<div>
<div class="content wysiwyg-content">
Need to update several columns and tables in a database and convert scientific notation to a decimal(27,17).<br />
<br />
Using the following for COLUMN(S) for the scientific notation part: <br />
<br />
CASE <br />
&nbsp; &nbsp; &nbsp; WHEN COLUMN like '%E-%' THEN LTRIM(RTRIM(CAST(CAST(COLU<wbr />MN AS FLOAT) AS DECIMAL(18,18))))<br />
&nbsp; &nbsp; &nbsp; WHEN COLUMN like '%E+%' THEN NULL<br />
&nbsp; &nbsp; &nbsp; ELSE COLUMN<br />
END<br />
<br />
<br />
What is the best way to update existing columns? SS 2008 R2
</div>
</div>


Need to update several columns and tables in a database and convert scientific notation to a decimal(27,17).

Using the following for COLUMN(S) for the scientific notation part: 

CASE 
      WHEN COLUMN like '%E-%' THEN LTRIM(RTRIM(CAST(CAST(COLUMN AS FLOAT) AS DECIMAL(18,18))))
      WHEN COLUMN like '%E+%' THEN NULL
      ELSE COLUMN
END


What is the best way to update existing columns? SS 2008 R2

Need to update several columns and tables in a database and convert scientific notation to a decimal(27,17).

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.