CODEWITHSUNDEEP
sql
Paul Riker
Paul Riker

Reputation: 797

Joining varchar and nvarchar

I'm comparing account numbers in two different databases to make sure the account exists in both. The account field in one database is nvarchar and the other it's varchar. I do a cast to cast them both to varchar(12) and join them to see where there isn't a match. If there is an account number with less than 12 characters then it thinks it's not a match. I'm assuming the extra characters in each field are causing the issue?

table1 - accountnumber(nvarchar(255))
table2 - accountnumber(varchar(20))

select * from
table1
left outer join table2 on table2.accountnumber = table1.accountnumber

In this one example, both tables have an account with the number 12345678, but the join isn't working. I'm not sure if it's data type mismatch or white space or something else.

--Added-- I should add that the data in table2 actually originates from an Oracle database where it's stored as a varchar2(12 byte). I import it into a SQL Server database where it's stored as a varchar(20). I'm not sure if this makes a difference.

Upvotes: 1

Views: 16239

Answers (3)

Oak_3260548
Oak_3260548

Reputation: 2010

I ran into absolutely same case, I had even two sibling queries (one created as a copy of another), which both had this problem. Collation and types were no issue here.

Finally after a LOT of testing, one of the queries started to work without aparent changes, just re-written. When I retyped the IN part of the second query, it started to work too. So there was a problem with a hidden character accidentally typed somewhere in the query.

Upvotes: 0

Czar Pino
Czar Pino

Reputation: 6314

Your query works fine. This is perhaps a character encoding issue. Try using collate. See this previous SO answer which might help.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

Not sure where you are having a problem. This query should return matching account numbers (no need to CAST):

SELECT *
FROM YourTable 
   JOIN YourOtherTable ON YourTable.AccountNumber = YourOtherTable.AccountNumber

If your data has spaces, you can TRIM your data depending on your RDBMS -- LTRIM and RTRIM for SQL Server.

SELECT *
FROM YourTable 
   JOIN YourOtherTable ON RTRIM(LTRIM(YourTable.AccountNumber)) = RTRIM(LTRIM(YourOtherTable.AccountNumber))

Here is the SQL Fiddle.

Good luck.

Upvotes: 1

Related Questions