I am tring to join two tables based on account_id
but one of them is Char another account_id is int,
I tried below
1. Select cast( t2.account_id as int) , t1. account_id from A as t1 left join B as t2
on t1.account_id = t2.account_id
2. Select t1.account_id, t2.account_id from A as t1 left join B as t2
on cast(t2.account_id as int)= t1.account_id
Both of them got the same error which is
Bad character in format .
can anyone give me the advice, how should i change my query ?
If the cast to an INT fails there are some characters outside of '0' to '9'. You can check which rows fail using
SELECT account_id FROM A WHERE TO_NUMBER(account_id) IS NULL;
You might also try to cast the number to a string using TRIM(t1.account_id), if this works depends on the actual string stored in t2.account_id.
Anyway, this indicates a bad datamodel.