CODEWITHSUNDEEP
sql-server
k80sg
k80sg

Reputation: 2473

SQL Server + Select WHERE IN comma delimited string

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.

Upvotes: 2

Views: 7854

Answers (3)

k80sg
k80sg

Reputation: 2473

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, ','))

Upvotes: 2

user2647012
user2647012

Reputation: 64

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

Upvotes: 0

Yuriy Galanter
Yuriy Galanter

Reputation: 39807

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.

Upvotes: 4

Related Questions