Alex A
asked on
T_SQL: joining tables on nvarchar and int columns
We'd like to join 2 tables on common column and get the data from both of them. The problem is that even though the data in the columns is similar, the datatype is not the same. One is nvarchar and the other is int. Some data cannot be converted to int.
What is the best way to write the query in order to get matching rows from both tables?
Thank you in advance.
What is the best way to write the query in order to get matching rows from both tables?
Thank you in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could try in the other direction, i.e. convert int to nvarchar which should always work thus avoiding the patindex. (Mind you it's a pity you have to do this at all.)
select
*
from TableA a
inner join TableB b on a.nvarcolumn = convert(nvarchar,b.intcolu
which of these 2 columns is indexed? maximizing use of indexes may influence the choice you make.
select
*
from TableA a
inner join TableB b on a.nvarcolumn = convert(nvarchar,b.intcolu
which of these 2 columns is indexed? maximizing use of indexes may influence the choice you make.
CAST the NVarchar column to Int or CAST INT to NVARCHAR in where or Join (ON part).
It will work for you.
It will work for you.
use CAST or convert both data types to nvarchar.