How to find out Not Exists Value from comma separated IDs with single SQL ?

• Shubhajyoti Ghosh

  SSC Veteran

  Points: 203

  More actions

  April 26, 2011 at 12:21 pm

  #148965

  Example:

  1,2,3,16

  database value

  1

  2

  3

  4

  5

  6

  14

  15

  I want 16 as output

  Is it possible to find out with a single select Query.

• WayneS

  SSC Guru

  Points: 95460

  More actions

  April 26, 2011 at 1:25 pm

  #1316142

  DECLARE @test-2 VARCHAR(20);

  SET @test-2 = '1,2,3,16';

  DECLARE @MyTestTable TABLE (RowId INT);

  INSERT INTO @MyTestTable

  SELECT 1 UNION ALL

  SELECT 2 UNION ALL

  SELECT 3 UNION ALL

  SELECT 4 UNION ALL

  SELECT 5 UNION ALL

  SELECT 6 UNION ALL

  SELECT 14 UNION ALL

  SELECT 15;

  SELECT RowID = CONVERT(INT, Item)

  FROM dbo.DelimitedSplit8K(@test,',') ds

  EXCEPT

  SELECT RowId

  FROM @MyTestTable;

  See the link in my signature for the latest DelimitedSplit8k function.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Shubhajyoti Ghosh

  SSC Veteran

  Points: 203

  More actions

  April 27, 2011 at 12:16 pm

  #1316810

  Your solution is excellent