Reputation: 688
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
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
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
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))
Upvotes: 1
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