Finding names in string that are not in the database

Question

I have two lists of names:

1) In table 'Names'

   Carl
   Tom
   Dan
   Thomas
   Beneth

2) In variable @string

   Tom
   Thomas
   Ben
   John
   Peter

Tables:
Names
Columns:
NameId
Name

The first list is in my database, but the second is in a string. I'm looking for a query that can give me names from @string, which are NOT in table Names. In this example, this should be: Carl, Dan, Beneth. I have no idea how to do that. If it's possible - no-procedure will be great.

is this variable a string?, and in table, is the names in each row? — Jithin Shaji, Aug 13 '14 at 12:20
Use this: http://stackoverflow.com/a/19935594/246342 to return a table-from-string-split and query with NOT IN() — Alex K., Aug 13 '14 at 12:20
Thx for help. Make Answer from it. I understand that this matter is too complicated to do it without the procedure. — Bogusz Michałowski, Aug 13 '14 at 12:22
something like [this](http://stackoverflow.com/questions/10229575/mysql-select-where-in-list-and-not-in-list-in-the-same-sql) — germcd, Aug 13 '14 at 14:03
@Jithin Shaji - typeof variable "@string" is nvarchar(MAX). One name in one row of table. — Bogusz Michałowski, Aug 13 '14 at 14:34

score 1 · Answer 1 · edited May 23 '17 at 12:20

Adapting the code of the function recommended by Alex K to just a single execution, you'll need to do the following (take out the @Names table and replace with your actual table):

DECLARE @List VARCHAR(100)
SET @List = 'Tom, Thomas, Ben, John, Peter'

DECLARE @Names TABLE (Name VARCHAR(10))
INSERT INTO @Names (Name) VALUES ('Tom')
INSERT INTO @Names (Name) VALUES ('Thomas')

SELECT [Value] 
FROM 
  ( 
    SELECT 
        [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],CHARINDEX(',', @List + ',', [Number]) - [Number])))
    FROM 
      (
        SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
        FROM sys.all_objects
      ) AS x
    WHERE 
        Number <= LEN(@List) AND 
        SUBSTRING(',' + @List, [Number], LEN(',')) = ','
  )  y
     LEFT JOIN 
    @Names n ON 
        y.Value = n.Name
WHERE n.Name IS NULL

score 1 · Accepted Answer · answered Aug 13 '14 at 17:34

This is a SQL version (for Oracle) but you should be able to adapt it to other systems.

select names.name from (
select 1 as NameId, 'Carl' as Name from dual
union
select 2, 'Tom' from dual
union
select 3, 'Dan' from dual
union
select 4, 'Thomas' from dual
union
select 5, 'Beneth' from dual) names
, (select 'Tom Thomas Ben John Peter' as list from dual) string
where length(replace(list, name, '')) =  length(string.list)

Finding names in string that are not in the database

2 Answers2