T-SQL如何将逗号分隔的数字字符串转换为整数 [英] T-SQL How to convert comma separated string of numbers to integer

问题描述

我收到错误消息将nvarchar值‘23，24,3,45,91’转换为数据类型int时转换失败。该错误似乎是在ON子句上发生的。 E.ID是一个整数字段，而F.LegalIssue是一个用逗号分隔的整数的varchar字段。以下是带有该错误的代码。

I get the error "Conversion failed when converting the nvarchar value '23,24,3,45,91,' to data type int." The error seems to be occuring on the ON clause. E.ID is an integer field while F.LegalIssue is a varchar field of integers separated by commas. Below is the code with that error.

SELECT F.[FDTitle], E.PrimaryOpID as [FD Primary OP ID], F.County as [FD County], F.Status as [FD Status], F.IssueDate as [FD Date]
FROM [dbo].[tbl_FinalDetMain] F
LEFT OUTER JOIN [dbo].[tbl_lk_Exemptions_FD] E ON E.ID = F.LegalIssue
WHERE F.[FDNbr] = '2013-0041'

我已经尝试过下面的on子句代码，但是它仅返回一个整数值，而不是整个整数字符串。

I have tried the code below for the on clause, but it only returns one integer value, instead of the entire string of integers.

E.ID = cast(LEFT(F.LegalIssue,PATINDEX('%[^0-9]%',F.LegalIssue)-1) as int)

结果应包含五个以逗号分隔的整数。

The result should include five integers delimited by commas.

推荐答案

如果 LegalIssue 包含一串用逗号分隔的数字，则您真的想要一个关联表。缺少这一点，这是进行联接的一种方便（但效率不高）的方法：

If LegalIssue contains a string of comma-delimited numbers, then you really want an association table. Lacking that, here is a convenient (but not efficient) way to do the join:

SELECT F.[FDTitle], E.PrimaryOpID as [FD Primary OP ID], F.County as [FD County],
       F.Status as [FD Status], F.IssueDate as [FD Date]
FROM [dbo].[tbl_FinalDetMain] F LEFT OUTER JOIN
      [dbo].[tbl_lk_Exemptions_FD] E
      ON ','+F.LegalIssue+',' like '%,'cast(E.ID as varchar(255))+',%'
WHERE F.[FDNbr] = '2013-0041';

这会在列表前加上逗号，以免产生冲突，例如在 1,100中找到 10 ，1000。

This prepends and postpends the list with commas to avoid conflicts, such as finding "10" in "1,100,1000".

这篇关于T-SQL如何将逗号分隔的数字字符串转换为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！