Error converting data type 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

General SQL Server Forums

New to SQL Server Programming

Error converting data type varchar to numeric

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2008-06-13 : 16:54:04

Hi Group:

I try to JOin to tables: Enterprise has a decimal(5,3) and COHIER has varchar 5. All I am getting is an error on converting data. How can I fix this problem.

SELECT a.Security_Value, b.Enterprise_Number, b.Enterprise_Description
FROM dbo.COHIER a INNER JOIN
dbo.Enterprise b ON a.Security_Value = b.Enterprise_Number

Thanks!!!!!!

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-13 : 16:59:02

[code]
SELECT a.Security_Value, b.Enterprise_Number, b.Enterprise_Description
FROM dbo.COHIER a INNER JOIN
dbo.Enterprise b ON Cast(a.Security_Value As Varchar(5))= b.Enterprise_Number
[/code]

Chirag

http://www.chirikworld.com

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2008-06-13 : 17:01:35

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Thanks for is giving me the same error

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-13 : 17:05:41

I guess.. it should be like this way...


SELECT a.Security_Value, b.Enterprise_Number, b.Enterprise_Description
FROM dbo.COHIER a INNER JOIN
dbo.Enterprise b ON a.Security_Value = Cast(b.Enterprise_Number As Varchar(5))

Chirag

http://www.chirikworld.com

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2008-06-13 : 17:11:53

Thank you chiragkhabaria...works!!!!

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-06-13 : 17:28:15

Which means that COHIER has some non numeric data in the field.
If it is supposed to have only numbers then you need to find the offending records and clean them up.

"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

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-06-13 : 17:31:52

Here is an example to help you find them

create table #tmp (c1 varchar(5))
insert into #tmp
select 5 union all
select 6 union all
select 7.9 union all
select 2 union all
select 'Text' union all
select 1

select * from #tmp where Cast(c1 as float) < 100

-- Causes this error
-- Server: Msg 8114, Level 16, State 5, Line 1
-- Error converting data type varchar to float.

-- To find the record do this
select * from #tmp where IsNumeric(c1) = 0

drop table #tmp

"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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources