[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
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
Medium Priority
33 Views
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
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
• 2

LVL 25

Expert Comment

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
``````
0

LVL 70

Expert Comment

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

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

chaau earned 2000 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
``````
``````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
``````
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'
``````

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
``````
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
``````
0

## Featured Post

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Acâ€¦
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
###### Suggested Courses
Course of the Month12 days, 13 hours left to enroll