Error converting data type varchar to real

Hello,

I have varchar values in a table:
-3.679219e+00
3.227934e+00
-1.471200e+00
-9.013421e-02
-1.540126e+00
8.738047e-01
-3.143845e+00
-2.701182e+00
-1.720428e+00
4.000000e+00
2.985881e-01
-2.628800e+00
-2.160176e+00
4.000000e+00
8.068669e-01
-1.313093e+00
-3.878120e-01
3.090651e-01

.. and I want to ultimately convert the varchar data type to decimal numbers with a precision of 5.

When I run the following:
update BETA
set [LIVE_neut]=CASE WHEN CHARINDEX('e', LIVE_neut) != 0 THEN CAST(CAST(LIVE_neut AS REAL) AS DECIMAL(15,5)) ELSE CAST(LIVE_neut AS DECIMAL(15,5)) END

I get "Error converting data type varchar to real".

Could you tell me why and how to get it to work? Ultimately I want to change the data type in the table to decimal. Not just cast but have the data type remain varchar.

Thank you.

Rebecca
rss2Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Hmm, something odd is going on, since:

SELECT ISNUMERIC(-6.227533e-03)

shows as 1, not 0.  Perhaps there are some non-displayable characters imbedded in the data?  Please try this:

SELECT *
FROM BETA
-- chk for CR, LF and TAB chars.
WHERE CHARINDEX(CHAR(13), LIVE_neut) > 0 OR CHARINDEX(CHAR(10), LIVE_neut) > 0 OR CHARINDEX(CHAR(9), LIVE_neut) > 0
0
 
Scott PletcherSenior DBACommented:
You have one or more values that are not in a valid format for a real number.  Try this to identify and correct them, then your convert should work:


SELECT LIVE_neut
FROM BETA
WHERE ISNUMERIC(LIVE_neut) = 0
0
 
rafranciscoCommented:
I tried all your given values and it all worked fine, no errors encountered.  Try this one:

update BETA
set [LIVE_neut]=CASE WHEN CHARINDEX('e', LIVE_neut) != 0 THEN CAST(CAST(LIVE_neut AS REAL) AS DECIMAL(15,5)) ELSE CAST(LIVE_neut AS DECIMAL(15,5)) END
where isnumeric([LIVE_neut]) = 1
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
CORRECTION (D'OH):

Probably want to use SELECT * so that you know which row has the bad value :-).


ISNUMERIC() is not 100% accurate for just one data type, such as REAL, but you will definitely need to correct any values it identifies.  If you still get errors on the conversion, let me know, will need to use PATTERN() or something similar to find bad values.
0
 
rss2Author Commented:

SELECT LIVE_neut
FROM BETA
WHERE ISNUMERIC(LIVE_neut) = 0 and live_neut is not null

results in values that look like numbers..
-1.076363e+00
-4.000000e+00
2.168363e+00
2.408402e-01
-2.844289e+00
-1.031439e+00
1.617047e-01
8.369206e-01
-2.523568e-01
1.734791e+00
4.000000e+00
-1.154578e+00
-5.178761e-04
4.398118e-01
-9.172848e-03
2.238005e+00
2.497033e+00
1.742650e+00
-1.235752e+00
5.673627e-02
6.425310e-01
-2.254004e+00
5.468249e-01
2.652320e-01
1.380126e-01
4.842345e-01
-1.218977e+00
-2.295728e+00
1.196095e+00
-1.857952e-02
2.076703e+00
-3.765513e-01
-7.222005e-01
2.652133e-01
-5.193761e-01
-6.227533e-03
-2.604087e-01

What do I do with these? Thank you.
0
 
rss2Author Commented:
When I try to change the data type to decimal with a precision of 5 from within Enterprise Manager, on that column, I get the following error:
'BETA' table
- Unable to modify table.  
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting numeric to data type numeric.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

Thank you.
0
 
rafranciscoCommented:
Try changing it first to float.  Once it is already in float, update it to only have 5 decimal places.  Then once this is done, change the data type to decimal (15,5).
0
 
rss2Author Commented:
Thanks! That was the problem.
0
 
Scott PletcherSenior DBACommented:
Great!  Naturally you can use REPLACE(CHAR(10), LIVE_neut, ''), etc., to get rid of them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.