CODEWITHSUNDEEP
sql
irfan
irfan

Reputation: 1

sql query to find records not in other table

I have a table plist with a column list, which contains comma separated ids (1,2,3,4). I want the record of those members whose id is not in the comma separated list.

Upvotes: 0

Views: 119

Answers (1)

AnandPhadke
AnandPhadke

Reputation: 13486

The best way is to split the comma seperated list into a table and then search against it.

Here is the code to split the string:

DECLARE @YourTable table (RowID int, Layout varchar(200))
INSERT @YourTable VALUES (1,'1,2,3,4')

;WITH SplitSting AS
(
    SELECT
        RowID,LEFT(Layout,CHARINDEX(',',Layout)-1) AS Part
            ,RIGHT(Layout,LEN(Layout)-CHARINDEX(',',Layout)) AS Remainder
        FROM @YourTable
        WHERE Layout IS NOT NULL AND CHARINDEX(',',Layout)>0
    UNION ALL
    SELECT
        RowID,LEFT(Remainder,CHARINDEX(',',Remainder)-1)
            ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder))
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0
    UNION ALL
    SELECT
        RowID,Remainder,null
        FROM SplitSting
        WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0
)
SELECT RowID,part FROM SplitSting ORDER BY RowID

Upvotes: 1

Related Questions