带有特殊字符和前导零的varchar上的SQL JOIN [英] SQL JOIN on varchar with special characters and leading zeros

问题描述

我在varchar列上的联接有问题.

我将通过产品代码从Excel数据更新SQL数据，但是Twr_Kod是varchar，这是联接Excel数据的唯一方法.

当前导零时，转换为varchar也存在问题.

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)

我知道加入varchar列不是一个好的解决方案，但是有必要，所以请帮助说明为什么 cast 不转换.

解决方案

问题是因为您的excel列包含混合数据类型. 导入具有混合数据类型列的csv文件或excel文件时，它将用null替换非主要类型. (使用Oledb或Ace.Oledb)

解决方法

将IMEX=1;添加到您的openrowset连接字符串中，并添加一个包含文本值的虚拟第一行

在使用Openrowset导入错误中获取详细信息，按照我的回答/p>

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.

解决方案

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

这篇关于带有特殊字符和前导零的varchar上的SQL JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！