cannot convert varchar to numeric

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

cannot convert varchar to numeric

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2011-08-23 : 11:24:02

Hi folks,

I've been puzzled with this for some time and hope I can get help.

I have imported a text file to a table in SQL2000. all columns are varchar. I have a column that contains only numbers with decimal places... for example :
1023.39900

When I attempt to convert this field to a numeric field, I get the Error

'Error converting data type varchar to float.

I think it may be something to do with the fact that the values in the field are inbetween invisible charaters like so:

'         1023.39900    '

i've tried rtrim and ltrim but I still have trailing spaces at the end. I've tried to convert it with convert(decimal(18,8)field) but still have the error. I've used TOP 1 just to focus on the value above and still get the error.

Does anyone have an idea why i'm getting this problem and how it can be solved?

Thanks in advance

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-08-23 : 12:23:43

You will need to get rid of the 'invisible' characters. The ASCII function should give you the ascii codes.

CR and LF are usually good places to start:


UPDATE YourTable
SET YourColumn = REPLACE(REPLACE(YourColumn, CHAR(13), ''), CHAR(10), '')

Kristen
Test

22859 Posts

Posted - 2011-08-23 : 13:39:47

[code]SELECT ...
FROM MyTable
WHERE MyColumn LIKE '%[^ 0-9.]%'
[/code]
will find rows where the column contains something other than space, digits or ".", which would then be worth further investigation

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-24 : 01:31:57

it might even be a hard space so you might need this too

UPDATE YourTable
SET YourColumn = REPLACE(REPLACE(REPLACE(YourColumn, CHAR(13), ''), CHAR(10), ''),CHAR(160),'')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 02:41:22

And TAB I possibly - CHAR(9)

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 04:39:30

I've posted a Function in the Script library to "display" rogue characters in a column

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164618

cidr
Posting Yak Master

207 Posts

Posted - 2011-08-24 : 07:50:25

Thanks folks. This has been a pain in the back! I'll test each of your suggestions and get back to you

:)