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".

Answer 2

Using xml datatype, you can explode your string to integers like this. Good candidate for a user defined function I would say :-)

declare @test varchar(max)

set @test = '1,2,3,4,5'

select 
     T2.item.value('(./text())[1]','int') 
from
     (select convert(xml,'<items><t>'+replace(@test,',','</t><t>')+'</t></items>') as xmldoc)
as xmltable
     CROSS APPLY xmltable.xmldoc.nodes('/items/t') as T2(item)