For example, I have a list of values in a string:
'a', 'c', 'b', 'd'
From the data table, I got a column result like:
Result
'a'
'b'
how to write a sql which will return the values not in the table: 'c', 'd'
or
NewResult
'c'
'd'
?
It is also ok if other simple tools than sql can be used. I only need the result. Thanks!
Create FUNCTION F_SplitAsTable
(
@txt varchar(max)
)
RETURNS
@tab TABLE
(
ID Varchar(2000)
)
AS
BEGIN
declare @i int
declare @s varchar(2000)
Set @i = CHARINDEX(',',@txt)
While @i>1
begin
set @s = LEFT(@txt,@i-1)
insert into @tab (id) values (@s)
Set @txt=RIGHT(@txt,Len(@txt)-@i)
Set @i = CHARINDEX(',',@txt)
end
insert into @tab (id) values (@txt)
RETURN
END
GO
Declare @a table (Ch varchar(10))
insert into @a Values ('a')
insert into @a Values ('b')
Select s.* from dbo.F_SplitAsTable('a,b,c,d') s
left join @a a on a.Ch=s.ID
where a.Ch is NULL
Step 1: Load the search values in a temp table.
DECLARE @Search table (SearchFor char(1) not null)
INSERT @Search values ('a'), ('b'), ('c'), ('d')
(There are any number of ways to set this up, this is just the fastest to type)
Run a query like so:
SELECT SearchFor
from @Search
except select SearchIn
from DataTable
(Again, there are many forms that "in a not in b" queries can take.)
This will return everything in the first set (your temp table) that is not also found in the second set.
use the not in clause in your queries.
select myCol from myTable where myCol not in ('c','d')
select myCol from myTable where myCol not in (select myCol from otherTable)
