Conversion failed when converting the varchar value 'id' to data type int

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

I got an error "Conversion failed when converting the varchar value 'Id' to data type int"....

What I have tried:

SELECT *
FROM History
INNER JOIN Header
ON History.Id = Header.docId

Posted 8-Feb-17 17:00pm

jhovyn

Updated 8-Feb-17 23:48pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2017-02-08T17:50:00

The error is giving you all the information you need to troubleshoot this. In one table, you've got an ID column that contains a numeric value, an integer. In the other table, the ID column is a string of characters. You cannot JOIN on the two different data types.

You COULD put an implicit conversion on the varchar column to match the integer on the other column but since the conversion has to be applied to every value in the varchar ID column, the JOIN operation is going to be pretty slow. On top of that, what if the ID column has a NULL in it or a value that cannot be converted to an integer value? Oops.

Karthik_Mahalingam · Accepted Answer · 2017-02-08T18:21:00

Solution 2

check this

SQL

 declare @History table (Id varchar(50),Name varchar(50))
declare @Header table (docId int,Name varchar(50))
insert into @History(id,name)values( 'id', 'a')
insert into @History(id,name)values( '2', 'a')

insert into @Header(docId,name)values( '2', 'a1')
insert into @Header(docId,name)values( '2', 'b1')

 
 select * from (  (SELECT * FROM @History where ISNUMERIC(id) =1 ) hi   INNER JOIN @Header he ON  hi.id = he.docId )

use ISNUMERIC (Transact-SQL)[^] to check the value is a valid number or not

Posted 8-Feb-17 18:21pm

Karthik_Mahalingam

Updated 8-Feb-17 18:22pm

v2

Comments

Richard Deeming 9-Feb-17 6:40am

ISNUMERIC considers many non-numeric strings to be numeric - for example, ',', '.', '-', etc.

For SQL 2012 or higher, it would be better to use TRY_PARSE[^]:

SELECT * FROM @History hi INNER JOIN @Header he ON he.docId = TRY_PARSE(hi.id As int)

For older versions, a simple NOT LIKE comparison is more robust for basic integers:

WHERE Id NOT LIKE '%[^0-9]%'

Karthik_Mahalingam 9-Feb-17 7:05am

Thanks for the info Richard