How to handle a data column in a JOIN that is NOT Numeric when the JOIN is expecting NUMERIC

Question

We have our DynamicsCRM passing us a VARCHAR(100) data column as [ID]. We have our Source Database expecting that [ID} to be Numeric. If it is not, ": 1363108", then how can we handle the LEFT JOIN of our Source Table ON [ID] that is NOT NUMERIC?

Don't want to get into cleaning up the data..."Garbage In....Garbage Out"...So the LEFT JOIN should thus just result in a <NULL> row but NOT throw out the data since it cannot find a match since it is indeed NOT NUMERIC.

Do I need to handle this as like an internal CASE and using ISNUMERIC? But I know ISNUMERIC will simply pass back a BIT Data Type..."1" = Yes...It is Numeric..."0"...No...It is NOT Numeric.

Thanks for your review and hopeful for a quick reply.

Answer 1

Use try_convert:

FROM goodtable g
LEFT JOIN badtable b ON g.numericid = try_convert(int, b.stringid)

Answer 2

Hi @Bobby P ,

Try with below code:

SELECT *  
    FROM TABLE1 T LEFT JOIN TABLE2 TT  
    ON T.NUMCLMN=CAST(TT.CHARCLMN AS NUMERIC)  

cast-and-convert-transact-sql

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.