?
Solved

Error converting data type varchar to real

Posted on 2005-04-27
9
Medium Priority
?
6,707 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:rss2
  • 4
  • 3
  • 2
9 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13877689
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13877700
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13877708
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rss2
ID: 13877781

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
 

Author Comment

by:rss2
ID: 13877909
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
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13877932
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 13877947
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
 

Author Comment

by:rss2
ID: 13877976
Thanks! That was the problem.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13878047
Great!  Naturally you can use REPLACE(CHAR(10), LIVE_neut, ''), etc., to get rid of them.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

862 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