How can I join on a CSV varchar?

Question

I have a varchar field that contains a string like "10,11,12,13". How can I use that CSV string to join to another table with those IDs? Here's the approach I'm taking now:

select *
from SomeTable a
WHERE (',' + @csvString + ',') LIKE '%,' + CONVERT(varchar(25), a.ID) + ',%'

Where @csvString is "10,11,12,...". I intend to use this method as a join condition as well.

That method works, but it's rather slow (using CAST doesn't improve the speed).

I understand that having CSVs in the database like that is usually a very silly idea in most cases, but there's nothing I can do about that.

Answer 1

You need a split function. There are many examples of such things on the web. Here's just one: http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx

Answer 2

There might be a more efficient way to do this with a CTE or Table variable...

CREATE FUNCTION dbo.[SplitIds]
(
  @identities varchar(2000)
)
RETURNS 
@IdList table
(
  ID int
)
AS
BEGIN
  DECLARE @ID varchar(10), @idx int

  SELECT @identities = LTRIM(RTRIM(@identities))+ ','
  SELECT @idx = CHARINDEX(',', @identities, 1)

  IF REPLACE(@identities, ',', '') <> ''
  BEGIN
    WHILE @idx > 0
    BEGIN
      SELECT @ID = LTRIM(RTRIM(LEFT(@identities, @idx - 1)))
      IF @ID <> ''
      BEGIN
        INSERT INTO @IdList (ID) 
        VALUES (CAST(@ID AS int))
      END
      SET @identities = RIGHT(@identities, LEN(@identities) - @idx)
      SET @idx = CHARINDEX(',', @identities, 1)
    END
  END  
  RETURN
END
GO

DECLARE @string varchar(2000)

SELECT @string = identities FROM MyTable WHERE Id = 1

SELECT * 
FROM dbo.People 
WHERE Id IN 
(
 SELECT ID FROM dbo.SplitIds(@string)
)