converting varchar to float - SQL Server Forums

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

SQL Server Development (2000)

converting varchar to float

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-02-29 : 13:51:28

I'm having issues with converting a varchar to float. Why do I wish to do this, you might say? I have two tables: One field is a varchar, which displays only numbers, not letters; the other is a float. I want to do an inner join on these two tables ON Fieldfloat = Fieldvarchar. The problem is the results spits out "Error converting data type nvarchar to float." Why? I suspect it's because some of the varchar values contain commas (ex: 1,000, 5,000, 10,000).

So, if Fieldfloat is 1000 and Fieldvarchar is 1,000, I'd like to compare the two to join the tables. Is there anyway to do this? Cast and convert is not working for me.

thank you.

update: maybe I have to write a stored procedure to do this. Perhaps I can search through the varchar value, find the comma position, and then use substrings to convert the number. For example, the comma position for value = 1,000 would be 2 and then i'd use substring(value, 1, 1) + substring(value, 3, 5) to return 1000. My question is how can I search through a string to find the positions of characters or even the length of the string?

thank you

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-02-29 : 16:03:12

i managed to write something that's very close to what I want, but I need a little help with it.

select AMT = substring(Amount, 1, Comma_position-1)
+ substring(Amount,Comma_Position+1, LEN(Amount))
from (select Amount, Comma_Position = isnull(nullif(charindex(',', Amount), 0), 2)
from DollarConversion)subquery

I have values of 250, 500, 1,000, 2,000 and 2,500 currently. The problem is the above query returns back 20, 50, 1000, 2000, 2500. The 3 latter numbers are good. But the first two are incorrect. They should read 250 and 500, respectively, but the 2nd substring is fudging it up and I'm not sure what to do at this point. Any help would be appreciated.

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-29 : 16:13:24

Cast(replace(YOUR_VARCHAR_COLUMN, ',','') as float)

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2008-02-29 : 16:34:28

boy, do i feel really stupid. thank you for this tip, though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources