How to search for non existing rows WITH VARIABLE

Hi,
I have a plain query that returns NON EXISTING rows in my table, which works nicely.
It goes along these lines:

select t.phone, u.id
from (
  values ('+3361234598'),('+3369999999'),('+3342094232424')
 as t(phone)
  left join users u on u.phone = t.phone
where u.phone is  null;

However, I have been trying to replace the (’+3361234598’),(’+3369999999’),(’+3342094232424’) part with a variable field, so that other non-developer users can just copy paste any list of phone numbers and get results (like what happens for normal variable filters).
I see there was a related question (How use multiple search in variable) but to clarify, I am looking to identify users who left their phone number in another list, outside my users database, but have NOT created their account in my users database, so that I can then follow up with them. Therefore, I want to copy paste the list of all phone numbers from my external list in the “variable” field and for Metabase to return only those non-registered-yet users. The field filter works for returning those that HAVE registered.
I have tried for instance writing: values {{phonelist}} and then in the variable “phonelist” writing exactly ('+3361234598'),('+3369999999'),('+3342094232424')
but that (unsurprisingly) doesn’t work.

Hi @Vaite
Metabase has two different types of variables - simple and field filter. Check the documentation for a lengthy explanation:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
The reason why your {{phonelist}} didn’t work is because Text Filter adds quotation marks around the variable, so everything inside the variable would just be a string.

The optimal way for your users would probably be Field Filters, but since it sounds like you don’t have the phone numbers, then your best solution would be simple Text Filter, which you would then split by comma, so the users would just enter a string like this: +3361234598,+3369999999,+3342094232424

I don’t know which database you’re querying, but this is Postgres (haven’t tested the code):

select t.phone, u.id
from (select regexp_split_to_table({{phonelist}}, E',') as phone) as t
left join users u on u.phone = t.phone
where u.phone is null;
1 Like

Thank you so much, this works perfectly!