SQL 错误"使用 PATINDEX 将 nvarchar 值转换为数据类型 int 时转换失败"

Question

我正试图清理一些联系数据，他们到处都有电话分机。那些在接触域的是有问题的。我在查询中有一个CASE语句，其中一个WHEN子句给了我一个奇怪的错误。我正试着从联系人栏中去掉扩展名。

情况如下:

CASE
WHEN TRIM([Title]) LIKE 'EXT %'     THEN REPLACE(TRIM([Title]), 'EXT ', '')
WHEN TRIM([Title]) LIKE 'EXT. %'    THEN REPLACE(TRIM([Title]), 'EXT. ', '')
WHEN TRIM([Title]) LIKE 'EXT: %'    THEN REPLACE(TRIM([Title]), 'EXT: ', '')
WHEN TRIM([Title]) LIKE 'EXT.%'     THEN REPLACE(TRIM([Title]), 'EXT.', '')
WHEN TRIM([Title]) LIKE 'X %'       THEN REPLACE(TRIM([Title]), 'X ', '')
WHEN TRIM([Title]) LIKE 'X%'        THEN REPLACE(TRIM([Title]), 'X', '')
WHEN TRIM([Contact]) LIKE '% EXT %'     THEN TRIM(REPLACE([Contact], LEFT([Contact], PATINDEX('% EXT %', [Contact])-4), ''))
WHEN TRIM([Contact]) LIKE '% EXT. %'    THEN TRIM(REPLACE([Contact], LEFT([Contact], PATINDEX('% EXT. %', [Contact])-5), ''))
WHEN TRIM([Contact]) LIKE '% EXT.%'     THEN TRIM(REPLACE([Contact], LEFT([Contact], PATINDEX('% EXT.%', [Contact]))-4, ''))
WHEN TRIM([Contact]) LIKE '% EX%'       THEN TRIM(REPLACE([Contact], LEFT([Contact], PATINDEX('% EX%', [Contact])-2), ''))
WHEN TRIM([Contact]) LIKE '% X %'       THEN TRIM(REPLACE([Contact], LEFT([Contact], PATINDEX('% X %', [Contact]))-2, ''))
WHEN TRIM([Contact]) LIKE '% X%'        THEN TRIM(REPLACE([Contact], LEFT([Contact], PATINDEX('% X%', [Contact]))-1, ''))
ELSE ''
END AS [Ph Ext],

问题是LIKE '% EXT.%'的WHEN。我在值'DAVID' s EXT.106'上得到的错误是:

将nvarchar值'DAVID' s '转换为数据类型int时，转换失败。

我不明白为什么会出现错误。如果我把它分开，它可以工作，我用:

DECLARE @str varchar(50) = 'DAVID''S EXT.106'
PRINT @str
PRINT PATINDEX('% EXT.%', @str)
PRINT LEN(@str)
PRINT LEFT(@str, 8+4)
PRINT TRIM( REPLACE(@str, LEFT(@str, PATINDEX('% EXT.%', @str)+4), ''))
DAVID'S EXT.106
8
15
DAVID'S EXT.
106

Answer 1

您的left函数有圆括号问题，将其与上面的行进行比较:

THEN TRIM(REPLACE([Contact], LEFT([Contact], PATINDEX('% EXT. %', [Contact])-5), ''))
THEN TRIM(REPLACE([Contact], LEFT([Contact], PATINDEX('% EXT.%', [Contact]))-4, ''))
The -4 needs to be here --^ like the line above.

你实际上试图从nvarchar值中减去4, SQL Server试图隐式转换为int，因此出现错误。

你在其他几行上也有同样的问题——你需要转换(没有双关语的意思……)或者是用挑剔的眼光审视你的圆括号，让它们保持一致，问题就会变得明显。