T-SQL How to convert comma separated string of numbers to integer

Question

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'

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.

Answer 1

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';

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