0

I have an array of string say it is ["a1","a2","a3"] and want to pass this array into the query.

The purpose is not to select the elements from database that are in this array or not in this, but is to select the elements in this array that are not inside the database.

Assume there is a table that is 

create table table1(
id int primary key not null,
name text
);

in side the table1, it may have [1,"a1"],[2,"a2"],[3, "b3"],[11,"str2"],[14,"california"],[15,"US"],[16,"aus"]

when I pass the array ["a1","a2","a3"] into the query, it could find that element "a3" is not inside table1 as "a1" and "a2" can be found, but "a3" cannot.

I have thought a possible solution to write in sql but it seems I cannot write out the query

My thought is

 select [something] from [an array](/*this is something I passed from java or golang*/) 
where [something] not in (select column from a table)

My thought is that select column from a table is a collection and an array is also a collection, but when writing into query, I stuck at there

I just wonder whether it is possible that I could organise the query and leave the place as a "?" and if I pass that array into ?, it will find the elements that are inside the array, but not inside the table.

spartaslave
  • 125
  • 8
  • You're question is not clear! Can you explain little bit more and give some more examples with dummy code. – Kaushik Apr 03 '19 at 04:31
  • 1
    You can create a `temp` table using your array details. and then do the desired query. – Kaushik Apr 03 '19 at 04:42

1 Answers1

0

Your best course of action here is to use a Table function like this one:

T-SQL split string

Then pass your array to your query in the form of a coma separated string.

For example:

SELECT name FROM dbo.mySplitFunction('a1,a2,13') WHERE name NOT IN (SELECT DISTINCT myField FROM myTable)