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.

Please stop storing comma-separated lists in a single column. You may think this is a clever and useful design but it causes more problems than it solves. The database is not a JSON container! — Aaron Bertrand, Sep 11 '13 at 14:57

score 9 · Accepted Answer · answered Sep 11 '13 at 14:55

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

score 3 · Answer 2 · answered Sep 11 '13 at 15:34

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)

Adriaan Stander · Answer 3 · 2013-09-11T15:08:35.793

You will either have to normalize the F.LegalIssue into many rows or you would have to use a LIKE

Something like

    CAST(E.ID A VARCHAR(50)) = F.LegalIssue
OR  F.LegalIssue LIKE CAST(E.ID A VARCHAR(50)) + ',%'
OR  '%,' + F.LegalIssue LIKE CAST(E.ID A VARCHAR(50)) + ',%'
OR  '%,' + F.LegalIssue LIKE CAST(E.ID A VARCHAR(50))

As you can see, the actual design of the table is the issue. You should rather avoid the design you have currently, and opt for a 1 to many or many to many design.

Here is a DEMO of how to flatten the values using a recursive CTE

SQL Fiddle DEMO

Creating test table and data

CREATE TABLE Tada(
  ID INT,
  SomeCommaString VARCHAR(50)
  )
INSERT INTO Tada Values (1, '10'),(2,'5,6,12,16')

Flattening the table

;WITH Vals AS (
  SELECT 
      ID,
      CASE
          WHEN CHARINDEX(',',SomeCommaString) = 0
            THEN SomeCommaString
          WHEN CHARINDEX(',',SomeCommaString) > 0
            THEN LEFT(SomeCommaString,CHARINDEX(',',SomeCommaString) - 1)
      END Val,

      CASE
          WHEN CHARINDEX(',',SomeCommaString) > 0
            THEN RIGHT(SomeCommaString,LEN(SomeCommaString) - CHARINDEX(',',SomeCommaString))
          ELSE NULL
      END Remainder
  FROM Tada
  UNION ALL
  SELECT 
      ID,
      CASE
          WHEN CHARINDEX(',',Remainder) = 0
            THEN Remainder
          WHEN CHARINDEX(',',Remainder) > 0
            THEN LEFT(Remainder,CHARINDEX(',',Remainder) - 1)
      END Val,

      CASE
          WHEN CHARINDEX(',',Remainder) > 0
            THEN RIGHT(Remainder,LEN(Remainder) - CHARINDEX(',',Remainder))
          ELSE NULL
      END Remainder

  FROM Vals
  WHERE Remainder IS NOT NULL
)
SELECT ID, Val
FROM Vals

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

3 Answers3

SQL Fiddle DEMO

Linked