SQL出现Conversion failed nvarchar int

暗香疏影 创作者
  2019-12-02 2019-12-02 创建   2019-12-02 2019-12-02 更新      

序

Conversion failed when converting the nvarchar value ‘abcdef’ to data type int.

表中其中值存在abc和123

这是因为表中值有abc也有123导致无法转换。

例子:

| HEADER1 | Column | HEADER3 | HEADER4 |
| content | abc | content | content |
| content | bbc | content | content |
| content | 123 | content | content |
| content | 345 | content | content |

解决办法

原文：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT pbi.[PID]
      ,pbi.[SN]
      ,pbi.[FN]
      ,pbi.[G]
      ,pbi.[F]
      ,pbi.[YG]
      ,pbi.[H]
      ,pbi.[PS]
      ,pbi.[AN]
	  ,um.PUID, um.UN,ulh.IPA,ulh.LDate
  FROM [TestingTable].[dbo].[View_ContactDetail] as pbi
 Left join UserMaster um on pbi.AN=um.PUID 
 LEFT JOIN [UserLoginHistory] ulh on um.PUID = ulh.PUID AND ulh.LoginNumber = 1
 WHERE um.PUID is not null

添加排除abc字母 

1
and um.PortalUserID not like '%[^0-9]%'

修改后 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT pbi.[PID]
      ,pbi.[SN]
      ,pbi.[FN]
      ,pbi.[G]
      ,pbi.[F]
      ,pbi.[YG]
      ,pbi.[H]
      ,pbi.[PS]
      ,pbi.[AN]
	  ,um.PUID, um.UN,ulh.IPA,ulh.LDate
  FROM [TestingTable].[dbo].[View_ContactDetail] as pbi
 Left join UserMaster um on pbi.AN=um.PUID and um.PortalUserID not like '%[^0-9]%'
 LEFT JOIN [UserLoginHistory] ulh on um.PUID = ulh.PUID AND ulh.LoginNumber = 1
 WHERE um.PUID is not null
  • 标题: SQL出现Conversion failed nvarchar int
  • 作者: 暗香疏影
  • 创建于 : 2019-12-02 00:00:00
  • 更新于 : 2019-12-02 00:00:00
  • 链接: https://blog.23ikr.com/2019/12/02/2019-12-02-SQL-Conversion-failed-nvarchar-int/
  • 版权声明: 本文章采用 CC BY-NC-SA 4.0 进行许可。
评论
目录
SQL出现Conversion failed nvarchar int
  1. 序
  2. 表中其中值存在abc和123
  3. 解决办法