Chicago1988
Chicago1988

Reputation: 688

Error converting data type varchar to numeric in join

I have the next query

SELECT *
FROM   Assoc AS cc 
       JOIN Orders AS o    ON cc.Referencecode = o.Orderid

I got this error: Error converting data type varchar to numeric.

I solved by adding this clause…

WHERE  ISNUMERIC (cc.Referencecode) = 1

My question is… is it ok this solution? (I don’t havea access to change the datatypes…) or should I do a convert in the join itself?

ps: ReferenceCode is varchar(50) OrderId is decimal (18,0)

Upvotes: 0

Views: 8571

Answers (4)

ATUL SHARMA
ATUL SHARMA

Reputation: 184

CAST(Varchar AS INT) You should convert it before comparing. If it is varchar.

Sample query:

SELECT *
FROM   Assoc AS cc Nvarchar(MAX)
       JOIN Orders AS o    ON cc.Referencecode = CAST(o.Orderid AS Nvarchar(MAX))

CAST in this way which ever gives you error.

Upvotes: 0

JMabee
JMabee

Reputation: 2300

I know this might be a little more work, but given the query you have will not scale very well once you have a large amount of data.

But if possible I would add a computed column such as

ALTER TABLE dbo.Assoc
  ADD Referencecode_DEC AS ISNULL(TRY_CONVERT(decimal(18,0), Referencecode, 0) PERSISTED 

Add an index on your new column and when you run the query:

SELECT *
FROM   Assoc AS cc 
   JOIN Orders AS o    ON cc.Referencecode_DEC = o.Orderid

It will use the correct statistics and should produce a much better plan

Upvotes: 0

S3S
S3S

Reputation: 25122

ISNUMERIC() could give you false positives here. For example, run these queries...

SELECT 'TRUE' WHERE ISNUMERIC('$') = 1
SELECT 'TRUE' WHERE ISNUMERIC('1e4') = 1

Instead, it would be safer to use the following where clause, which removes columns that contain anything other than a digit.

WHERE cc.Referencecode not like '%[^0-9]%'

If you want to include decimals, you can use:

WHERE cc.Referencecode not like '%[^0-9.]%'

Or for SQL Server 2012 / 2016

JOIN Orders AS o ON TRY_CONVERT(NUMERIC,cc.Referencecode) = o.Orderid

Lastly, you could convert them both to varchar, to not remove any rows.

JOIN Orders AS o ON cc.Referencecode = CAST(o.Orderid as VARCHAR(4000))

TRY_CONVERT() Reference

Upvotes: 1

Ashu
Ashu

Reputation: 482

I will prefer to do as below:-

SELECT *
FROM   Assoc AS cc 
       JOIN Orders AS o    ON cc.Referencecode = CAST(o.Orderid as Nvarchar(MAX))

Upvotes: 0

Related Questions