Post reply

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

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

  • April 26, 2011 at 1:25 pm

    #1316142

    DECLARE @test VARCHAR(20);

    SET @test = '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

  • April 27, 2011 at 12:16 pm

    #1316810

    Your solution is excellent

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply