SQL Server + Select WHERE IN comma delimited string

Question

I am trying to select records where the Id exists in a comma delimited string but the query below doesn't return any results:

  SELECT * FROM [SellPost] WHERE CAST([AutoId] AS nchar(100)) 
   IN (SELECT [SellPostId] FROM [SellPostViewHistory]) -- SellPostId contains the delimited string 

The statement 'SELECT [SellPostId] FROM [SellPostViewHistory]' will return 19,20,21 and if I replace the query with

   SELECT * FROM [SellPost] WHERE CAST([AutoId] AS nchar(100)) 
   IN (19,20,21) 

it works. Can someone please kindly advice. Thanks.

Answer 1

If your inner select returns an actual single string value, like '19, 20, 21', simple using IN won't work. You have to split it into table variable (search SO there're many options on how to do it).

Then you can either join your original query with this temp variable or run your IN statement against a SELECT from such table.

Answer 2

Ok probably most people already know this but still this is one of the solution:

Create a split function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split](
  @delimited NVARCHAR(MAX),
  @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
  DECLARE @xml XML
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

  INSERT INTO @t(val)
  SELECT  r.value('.','varchar(MAX)') as item
  FROM  @xml.nodes('/t') as records(r)
  RETURN
END

Usage:

 DECLARE @SellPostIds nvarchar(MAX)
   SET @SellPostIds = (SELECT [SellPostId] FROM [SellPostViewHistory])

   SELECT * FROM [SellPost] WHERE CAST([AutoId] AS nchar(100)) 
   IN (SELECT val FROM dbo.Split(@SellPostIds, ','))

Answer 3

You need a CSV to table split, you can have a UDF taking input as string and return a Table, make a join to these tables and work with your IN clause