October 31, 2018 at 4:33 am
Hi,
I have this task to convert the Exponential values to numeric(20,15) format. The problem I am facing is the values range from
3.2569232735668707E-3 (which is 3.2569232735668707 * 10/ 1000) to
|
October 31, 2018 at 4:44 am
pwalter83 - Wednesday, October 31, 2018 4:33 AMHi,I have this task to convert the Exponential values to numeric(20,15) format. The problem I am facing is the values range from
3.2569232735668707E-3 (which is 3.2569232735668707 * 10/ 1000) to
-1.1102230246251565E-16 (which is -1.1102230246251565 * 10/10000000000000000) At the same time, there are also values in the same column which don't have Exponential values like -
-0.85416345709443842 so basically its a mix of both. The requirement is to convert both the Exponential and non-exponential values to numeric(20,15) type.
I have tried the following but it doesnt work as desired as the values returned are upto 6 decimal places only.
SUBSTRING(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT, 1, (INSTR(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT,'E',1)-1))::NUMERIC(38,31)/RPAD(1,(SUBSTRING(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT,INSTR(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT,'E',1)+2))+1,0)
Could somebody please have any idea on how to get upto 15 decimal places by changing the above ? Thanks.
Are you importing the values from Excel?
😎
October 31, 2018 at 5:15 am
Eirikur Eiriksson - Wednesday, October 31, 2018 4:44 AMpwalter83 - Wednesday, October 31, 2018 4:33 AMHi,I have this task to convert the Exponential values to numeric(20,15) format. The problem I am facing is the values range from
3.2569232735668707E-3 (which is 3.2569232735668707 * 10/ 1000) to
-1.1102230246251565E-16 (which is -1.1102230246251565 * 10/10000000000000000) At the same time, there are also values in the same column which don't have Exponential values like -
-0.85416345709443842 so basically its a mix of both. The requirement is to convert both the Exponential and non-exponential values to numeric(20,15) type.
I have tried the following but it doesnt work as desired as the values returned are upto 6 decimal places only.
SUBSTRING(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT, 1, (INSTR(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT,'E',1)-1))::NUMERIC(38,31)/RPAD(1,(SUBSTRING(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT,INSTR(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT,'E',1)+2))+1,0)
Could somebody please have any idea on how to get upto 15 decimal places by changing the above ? Thanks.
Are you importing the values from Excel?
😎
No, actually I am loading the data from a staging table to another table in the same database.
Its just that the data needs to be formatted as per the business users requirement before loading. Thanks.
October 31, 2018 at 6:25 am
pwalter83 - Wednesday, October 31, 2018 5:15 AMEirikur Eiriksson - Wednesday, October 31, 2018 4:44 AMpwalter83 - Wednesday, October 31, 2018 4:33 AMHi,I have this task to convert the Exponential values to numeric(20,15) format. The problem I am facing is the values range from
3.2569232735668707E-3 (which is 3.2569232735668707 * 10/ 1000) to
-1.1102230246251565E-16 (which is -1.1102230246251565 * 10/10000000000000000) At the same time, there are also values in the same column which don't have Exponential values like -
-0.85416345709443842 so basically its a mix of both. The requirement is to convert both the Exponential and non-exponential values to numeric(20,15) type.
I have tried the following but it doesnt work as desired as the values returned are upto 6 decimal places only.
SUBSTRING(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT, 1, (INSTR(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT,'E',1)-1))::NUMERIC(38,31)/RPAD(1,(SUBSTRING(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT,INSTR(AVERAGE_SALES_PRICE_PRIOR_PERIOD_PCT,'E',1)+2))+1,0)
Could somebody please have any idea on how to get upto 15 decimal places by changing the above ? Thanks.
Are you importing the values from Excel?
😎No, actually I am loading the data from a staging table to another table in the same database.
Its just that the data needs to be formatted as per the business users requirement before loading. Thanks.
What is the datatype of the column in the staging table?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2018 at 7:35 am
Suggest you do this in the database, here is a suggestion for a solution.
😎
Note, you'll need the scale of minimum 19 as in this case = (1 + [Number of fraction digits] - (-E)) = 1+ 15 + 3 = 19.
USE TEEST;
GO
SET NOCOUNT ON;
WITH SAMPLE_DATA(NUMEX) AS
(
SELECT '3.2569232735668707E-3' UNION ALL
SELECT '0.32569232735668707E-2' UNION ALL
SELECT '32.569232735668707E-4' UNION ALL
SELECT '325.69232735668707E-5' UNION ALL
SELECT '3256.9232735668707E-6'
)
SELECT
SD.NUMEX
,CONVERT(NUMERIC(20,19),CONVERT(FLOAT,SD.NUMEX,0),0) AS FNUM
FROM SAMPLE_DATA SD;
Output
NUMEX FNUM
---------------------- ---------------------
3.2569232735668707E-3 0.0032569232735668707
0.32569232735668707E-2 0.0032569232735668707
32.569232735668707E-4 0.0032569232735668707
325.69232735668707E-5 0.0032569232735668707
3256.9232735668707E-6 0.0032569232735668707
October 31, 2018 at 7:55 am
Eirikur Eiriksson - Wednesday, October 31, 2018 7:35 AMSuggest you do this in the database, here is a suggestion for a solution.
😎Note, you'll need the scale of minimum 19 as in this case = (1 + [Number of fraction digits] - (-E)) = 1+ 15 + 3 = 19.
USE TEEST;
GO
SET NOCOUNT ON;WITH SAMPLE_DATA(NUMEX) AS
(
SELECT '3.2569232735668707E-3' UNION ALL
SELECT '0.32569232735668707E-2' UNION ALL
SELECT '32.569232735668707E-4' UNION ALL
SELECT '325.69232735668707E-5' UNION ALL
SELECT '3256.9232735668707E-6'
)
SELECT
SD.NUMEX
,CONVERT(NUMERIC(20,19),CONVERT(FLOAT,SD.NUMEX,0),0) AS FNUM
FROM SAMPLE_DATA SD;Output
NUMEX FNUM
---------------------- ---------------------
3.2569232735668707E-3 0.0032569232735668707
0.32569232735668707E-2 0.0032569232735668707
32.569232735668707E-4 0.0032569232735668707
325.69232735668707E-5 0.0032569232735668707
3256.9232735668707E-6 0.0032569232735668707
Thanks very much, its worked perfectly !
October 31, 2018 at 8:01 am
Jeff Moden - Wednesday, October 31, 2018 7:44 AMEirikur Eiriksson - Wednesday, October 31, 2018 7:35 AMSuggest you do this in the database, here is a suggestion for a solution.
😎Note, you'll need the scale of minimum 19 as in this case = (1 + [Number of fraction digits] - (-E)) = 1+ 15 + 3 = 19.
USE TEEST;
GO
SET NOCOUNT ON;WITH SAMPLE_DATA(NUMEX) AS
(
SELECT '3.2569232735668707E-3' UNION ALL
SELECT '0.32569232735668707E-2' UNION ALL
SELECT '32.569232735668707E-4' UNION ALL
SELECT '325.69232735668707E-5' UNION ALL
SELECT '3256.9232735668707E-6'
)
SELECT
SD.NUMEX
,CONVERT(NUMERIC(20,19),CONVERT(FLOAT,SD.NUMEX,0),0) AS FNUM
FROM SAMPLE_DATA SD;Output
NUMEX FNUM
---------------------- ---------------------
3.2569232735668707E-3 0.0032569232735668707
0.32569232735668707E-2 0.0032569232735668707
32.569232735668707E-4 0.0032569232735668707
325.69232735668707E-5 0.0032569232735668707
3256.9232735668707E-6 0.0032569232735668707I'm thinking there might be a slight flaw there, yes?
Not perfect but close enough 😉
😎
Given that there is only one significant digit, this will work to the limits of the float precision, the things to look out fore are that if the scale is higher than the the effective number of fraction digits, the values will not be exact and using this method, the max scale is 19 because of the intermediate float conversion. Exponential notations can only be directly converted from a character string into float/real.
October 31, 2018 at 8:03 am
Eirikur Eiriksson - Wednesday, October 31, 2018 8:01 AMJeff Moden - Wednesday, October 31, 2018 7:44 AMEirikur Eiriksson - Wednesday, October 31, 2018 7:35 AMSuggest you do this in the database, here is a suggestion for a solution.
😎Note, you'll need the scale of minimum 19 as in this case = (1 + [Number of fraction digits] - (-E)) = 1+ 15 + 3 = 19.
USE TEEST;
GO
SET NOCOUNT ON;WITH SAMPLE_DATA(NUMEX) AS
(
SELECT '3.2569232735668707E-3' UNION ALL
SELECT '0.32569232735668707E-2' UNION ALL
SELECT '32.569232735668707E-4' UNION ALL
SELECT '325.69232735668707E-5' UNION ALL
SELECT '3256.9232735668707E-6'
)
SELECT
SD.NUMEX
,CONVERT(NUMERIC(20,19),CONVERT(FLOAT,SD.NUMEX,0),0) AS FNUM
FROM SAMPLE_DATA SD;Output
NUMEX FNUM
---------------------- ---------------------
3.2569232735668707E-3 0.0032569232735668707
0.32569232735668707E-2 0.0032569232735668707
32.569232735668707E-4 0.0032569232735668707
325.69232735668707E-5 0.0032569232735668707
3256.9232735668707E-6 0.0032569232735668707I'm thinking there might be a slight flaw there, yes?
Not perfect but close enough 😉
😎
Given that there is only one significant digit, this will work to the limits of the float precision, the things to look out fore are that if the scale is higher than the the effective number of fraction digits, the values will not be exact and using this method, the max scale is 19 because of the intermediate float conversion. Exponential notations can only be directly converted from a character string into float/real.
Nah... my bad. I actually deleted that post because I made a mistake. Your stuff is fine especially considering the limitations of SQL Server for these things..
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply