SQL JOIN on varchar with special characters and leading zeros

Question

I have a problem with join on varchar column.

I'm going to update SQL data from Excel data by product code but Twr_Kod is varchar and it is the only way to join with Excel data.

There is also problem with convert to varchar when there are leading zeros.

select CDN.Towary.Twr_Kod, excel.Twr_Kod 
from CDN.Towary 
left join openrowset('Microsoft.ACE.OLEDB.12.0', 
                     Excel 8.0;Database=C:\excel\towary.xlsx;',
                     'select * from [Arkusz1$]') excel 
                  on cast(CDN.Towary.Twr_Kod as varchar) = cast(excel.Twr_Kod as varchar)

I know it is not a good solution to join on varchar column but it's necessary so please help why cast is not converting.

please give some example values with leading zeros and highlight the problem, you haven't shown that in your sample data — Tanner, Mar 02 '17 at 09:29
Here is Excel file with join result [link]http://www50.zippyshare.com/v/HFp71lW4/file.html — TronComputers, Mar 02 '17 at 09:35
please don't share files like this, nobody want's to click a link to download an excel file off some random person off the internet — Tanner, Mar 02 '17 at 09:50

score 0 · Accepted Answer · edited May 23 '17 at 12:09

0

The problem is because your excel column contains mixed data types. When importing csv file or excel files with mixed data types column it will replace non dominant types by null. (Using Oledb or Ace.Oledb)

Workaround

Add IMEX=1; to your openrowset connectionstring and add a dummy first row containning text values

Follow my answer at Import error using Openrowset to get more details

edited May 23 '17 at 12:09

Community

1
1

answered Mar 02 '17 at 09:46

Hadi

36,233
13
65
124

It's so confusing but working. Thank you so much. You saved my life :D – TronComputers Mar 02 '17 at 11:09

SQL JOIN on varchar with special characters and leading zeros

1 Answers1

Linked