• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1175
  • 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.
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.
convert both to the nvarchar is then best because to convert int error occurs
use CAST or convert both data types to nvarchar.
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.)

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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

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