Use array_construct in filter-CodePudding

I have a sql-query like this:

select
    field_name,
    field_value
from 
    my_table
where field_name not in (array_construct('A', 'B'))

The array_construct is something I get out of another column. I want to use it as a filter with an in-condition.

Do you have any idea on how to do this?

I get an error message like: Can not convert parameter 'ARRAY_CONSTRUCT('A', 'B')' of type [ARRAY] into expected type [VARCHAR(120)]

CodePudding user response：

Using ARRAY_CONTAINS function:

select
    field_name,
    field_value
from 
    my_table
where NOT ARRAY_CONTAINS(filed_name::VARIANT, array_construct('A', 'B'));

CodePudding user response：

A more compact form using []

where not arrays_overlap( [col1], [col2,col3] )