Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl (id int, a text[], b text[]);
INSERT INTO tbl
   VALUES
-- include:
  ( 0, NULL              , NULL              ) -- NULL
, ( 1, '{}'              , '{}'              ) -- empty
, ( 2, '{000000, 000000}', '{000000, 000000}') -- none
, ( 3, '{000000, abc000}', '{000000, 000000}') -- a has abc
, ( 4, '{000000, 000000}', '{000000, 00xyz0}') -- b has xyz
, ( 5, '{000000, 000000}', '{abc000, 00xyz0}') -- b1 has abc, b2 has xyz
, ( 6, '{0abcxy, 000000}', '{z00000, 000000}') -- a1 || b1 would produce false positive
-- exclude:
, ( 7, '{ababc0, 000000}', '{xyz000, 000000}') -- a1 has abc, b1 has xyz
, ( 8, '{000000, abc000}', '{000000, 000xyz}') -- a2 has abc, b2 has xyz
, ( 9, '{abc000, 000000}', '{000000, 00xyz0}') -- a1 has abc, b2 has xyz
, (10, '{abc000, 000000}', '{000000, 00xyz0}') -- a2 has xyz, b3 has abc
;
11 rows affected

-- test against concatenated strings with CROSS JOIN
SELECT t.*
FROM   tbl t, array_to_string(a, '|') AS a_string, array_to_string(b, '|') AS b_string
WHERE (a_string LIKE '%abc%' AND b_string LIKE '%xyz%'
   OR  a_string LIKE '%xyz%' AND b_string LIKE '%abc%') IS NOT TRUE;


id
a
b


0
null
null

1
{}
{}

2
{000000,000000}
{000000,000000}

3
{000000,abc000}
{000000,000000}

4
{000000,000000}
{000000,00xyz0}

5
{000000,000000}
{abc000,00xyz0}

6
{0abcxy,000000}
{z00000,000000}


-- test against concatenated strings with NOT EXISTS
SELECT *
FROM   tbl t
WHERE  NOT EXISTS (
   SELECT FROM array_to_string(a, '|') a_string
             , array_to_string(b, '|') b_string
   WHERE  a_string LIKE '%abc%' AND b_string LIKE '%xyz%'
      OR  a_string LIKE '%xyz%' AND b_string LIKE '%abc%'
   );


id
a
b


0
null
null

1
{}
{}

2
{000000,000000}
{000000,000000}

3
{000000,abc000}
{000000,000000}

4
{000000,000000}
{000000,00xyz0}

5
{000000,000000}
{abc000,00xyz0}

6
{0abcxy,000000}
{z00000,000000}


-- test unnested elements with LATERAL subquery
SELECT t.*
FROM   tbl t
JOIN   LATERAL (
   SELECT bool_or(a_elem LIKE '%abc%') AND bool_or(b_elem LIKE '%xyz%') 
       OR bool_or(a_elem LIKE '%xyz%') AND bool_or(b_elem LIKE '%abc%') AS exclude
   FROM   unnest(t.a,t.b) e(a_elem, b_elem)
   ) x ON exclude IS NOT TRUE;
   


id
a
b


0
null
null

1
{}
{}

2
{000000,000000}
{000000,000000}

3
{000000,abc000}
{000000,000000}

4
{000000,000000}
{000000,00xyz0}

5
{000000,000000}
{abc000,00xyz0}

6
{0abcxy,000000}
{z00000,000000}


-- test unnested elements with NOT EXISTS
SELECT *
FROM   tbl t
WHERE  NOT EXISTS (
   SELECT FROM unnest(t.a,t.b) AS e(a_elem, b_elem)
   HAVING bool_or(a_elem LIKE '%abc%') AND bool_or(b_elem LIKE '%xyz%') 
       OR bool_or(a_elem LIKE '%xyz%') AND bool_or(b_elem LIKE '%abc%')
   );


id
a
b


0
null
null

1
{}
{}

2
{000000,000000}
{000000,000000}

3
{000000,abc000}
{000000,000000}

4
{000000,000000}
{000000,00xyz0}

5
{000000,000000}
{abc000,00xyz0}

6
{0abcxy,000000}
{z00000,000000}
id	a	b
0	null	null
1	{}	{}
2	{000000,000000}	{000000,000000}
3	{000000,abc000}	{000000,000000}
4	{000000,000000}	{000000,00xyz0}
5	{000000,000000}	{abc000,00xyz0}
6	{0abcxy,000000}	{z00000,000000}