Solved

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

Posted on 2014-01-22
4
29 Views
Last Modified: 2016-02-26
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
0
Comment
Question by:JGH5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39802098
Instead of doing the cast like this, I would recommend to use STR 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

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39803652
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
0
 

Author Comment

by:JGH5
ID: 39804285
Also, the data is already in the table as a varchar and needs to be converted to a decimal data type.
0
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 39804687
To convert data type from varchar to decimal you will have create a temporary column, like this:
ALTER TABLE Table1 ADD COLUMN tempColumn DECIMAL(18,8) NULL

Open in new window

Then, run your Update statement:
UPDATE Table1 SET tempColumn = 
CASE
      WHEN oldCOLUMN like '%E%' THEN CAST(CAST(oldCOLUMN AS FLOAT) AS DECIMAL(18,8))
      ELSE NULL -- actually, I think you do not need any case statements at all, see below
END 

Open in new window

Then run a statement to DROP old column and rename the column:
ALTER TABLE Table1 DROP COLUMN oldColumn;
exec sp_rename 'Table1.tempColumn', 'Table1.oldColumn'

Open in new window


Note: I think the conversion should take care for the cases where data is not numeric at all. In this case you could use ISNUMERIC() function to make sure that the data converts without any problems. Check for example this statement:
;with textData as(
  SELECT '123.5E+10' AS t UNION ALL
  SELECT '123.5E-10' AS t UNION ALL
  SELECT '123.5' AS t UNION ALL
  SELECT '-2312E+5' AS t UNION ALL
  SELECT 'abcd' AS t UNION ALL
  SELECT 'text' AS t )
select t, CASE WHEN ISNUMERIC(t)=1 
               THEN convert(decimal(27,8), convert(float, t))
               ELSE NULL END AS num
FROM textData

Open in new window

The correct Update statement should be:
UPDATE Table1 SET tempColumn = 
CASE
      WHEN WHEN ISNUMERIC(oldCOLUMN)=1 THEN convert(decimal(27,8), convert(float, oldCOLUMN))
      ELSE NULL
END 

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question