July 24, 2009 at 12:50 pm
Hi my data is coming in as text into nvarchar as it read from a text file.
declare @data_nv nvarchar(500)
declare @data_numeric DECIMAL(15,2)
SET @data_nv = '2.0018430861853059e-005'
select CONVERT(decimal(15,2) ,substring(@data_nv,1,10) )
This is 2.00 but it should be 0.0000277071
How to code this ...any ideas?
July 24, 2009 at 12:57 pm
The main issue is the substring: SELECT substring(@data_nv,1,10)
Maybe this will help:declare @data_nv nvarchar(500)
declare @data_numeric DECIMAL(15,2)
SET @data_nv = '2.0018430861853059e-005'
SELECT CAST(@data_nv AS FLOAT)
SELECT CAST(CAST(@data_nv AS FLOAT) AS DECIMAL(38,25))
SELECT CAST(CAST(CAST(@data_nv AS FLOAT) AS DECIMAL(38,25)) AS VARCHAR(38))
SELECT LEFT(CAST(CAST(CAST(@data_nv AS FLOAT) AS DECIMAL(38,25)) AS VARCHAR(38)), 10)
SELECT CAST(LEFT(CAST(CAST(CAST(@data_nv AS FLOAT) AS DECIMAL(38,25)) AS VARCHAR(38)), 10) AS DECIMAL(15,2))
-- But that is not what you want..
SELECT CAST(LEFT(CAST(CAST(CAST(@data_nv AS FLOAT) AS DECIMAL(38,25)) AS VARCHAR(38)), 10) AS DECIMAL(15,12))
July 24, 2009 at 1:25 pm
declare @data_nv nvarchar(500)
declare @data_numeric DECIMAL(15,2)
SET @data_nv = '2.0018430861853059e-005'
select CONVERT(decimal(15,2) ,substring(@data_nv,1,10) )
You need to substring twice. First 10 like you have plus everything past the 'e', meaning the -005.
Then convert both strings. The first to a double (x) the second to a int (y).
then the value = x * 10 ^ y
or 2.00184308 * 10 ^ -5 = 0.0000200184308
Clear?
July 24, 2009 at 1:53 pm
I'm not sure where the other responses here are coming from. You've actually got two main points to fix:
1. The format for defining a decimal datatype has the total number of digits followed by the number of digits to the right of the decimal point. Thus, "decimal(15,2)" defines an element of up to 15 total digits with 2 decimal places. You need at least 1 to the left, leaving up to 14 f to the right, so change that definition to "decimal(15,14)" or "decimal(15,10)" for fewer decimal places.
2. The Convert() function won't work directly from a string to decimal if the string is formatted as a floating point number. Nest two convert() functions to do this, and be sure to accomodate the full length of the string (23 characters in your example, not just 10).
I also don't see how you would expect a result of 0.0000277071, considering the input. Was this due to a typo or something else? Finally, your code didn't use the local variable @dataNumeric after defining it. If you do want to assign the result to that variable, fix its definition to hold more decimal places as well.
declare @data_nv nvarchar(500)
declare @data_numeric DECIMAL(15,2)
SET @data_nv = '2.0018430861853059e-005'
--select CONVERT(decimal(15,2) ,substring(@data_nv,1,10) ) -- <== original code doesn't work
select CONVERT(decimal(15,14),convert(float ,substring(@data_nv,1,23) )) -- resulta = 0.00002001843086
select CONVERT(decimal(15,10),convert(float ,substring(@data_nv,1,23) )) -- results = 0.0000200184
July 24, 2009 at 1:54 pm
Thinking.
Perhaps i have to do a function to pass in value and output the correct value.
Got 130 columns to do this on.
Sometimes it be a e-006 or even just a number 1.598181 etc
July 24, 2009 at 1:58 pm
Thanks.............that was what i wanted.
Just changed to 23 to the length of the field.
declare @data_nv nvarchar(500)
declare @data_numeric DECIMAL(15,2)
SET @data_nv = '2.0018430861853059e-005'
select CONVERT(decimal(15,14),convert(float ,substring(@data_nv, 1,len(@data_nv)) )) -- resulta = 0.00002001843086
select CONVERT(decimal(15,10),convert(float ,substring(@data_nv,1,len(@data_nv)) ))
Awesome
July 24, 2009 at 2:00 pm
If more than 23 in the string or less i should use len too
July 24, 2009 at 2:08 pm
I don't think you need to do anything special for the length of the number in the varchar unless there's somethnig else followinng the number. I just ran this using substring to get the first 30 charactiers and it worked fine. declare @data_nv nvarchar(500)
declare @data_numeric DECIMAL(15,14)
SET @data_nv = '2.0018430861853059e-005'
--SET @data_nv = '2.00184308'
--select CONVERT(decimal(15,2) ,substring(@data_nv,1,10) ) -- <== original code doesn't work
select CONVERT(decimal(15,14),convert(float ,substring(@data_nv,1,30) )) -- resulta = 0.00002001843086
select CONVERT(decimal(15,10),convert(float ,substring(@data_nv,1,30) )) -- results = 0.0000200184
July 24, 2009 at 2:16 pm
John's code is dead on, I was just pointing out that the substring of the first 10 characters would never provide the desired result or anything close to it.
😎
July 25, 2009 at 10:42 am
Yes that was great and i appreciated it. Got my program running Friday made my weekend.
Cheers for sharing.............
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply