• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1117
  • Last Modified:

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.
0
quasar_ee
Asked:
quasar_ee
1 Solution
 
Anthony PerkinsCommented:
Something like this:
FROM    TableA a
        INNER JOIN TableB b ON CASE PATINDEX('%[^0-9]%', a.nvarcharcolumn)
                                 WHEN 0 THEN CAST(a.nvarcharcolumn AS int)
                                 ELSE NULL
                               END = b.integercolumn 

Open in new window


Don't use ISNUMERIC() that is a bad idea.
0
 
sarabhaiCommented:
convert both to the nvarchar is then best because to convert int error occurs
use CAST or convert both data types to nvarchar.
0
 
PortletPaulCommented:
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.intcolumn)

which of these 2 columns is indexed? maximizing use of indexes may influence the choice you make.
0
 
Alpesh PatelAssistant ConsultantCommented:
CAST the NVarchar column to Int or CAST INT to NVARCHAR in where or Join (ON part).

It will work for you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now