Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE flights(id, path) AS VALUES
(1, 'NZ:EU'),
(2, 'JP:CA');
TABLE flights;

SELECT 2

id
path

1
NZ:EU

2
JP:CA

SELECT 2

SELECT *
FROM flights
WHERE path ILIKE ALL (ARRAY['%j%', '%p%', '%c%', '%a%']);

SELECT *
FROM flights
WHERE path ILIKE ALL (ARRAY['%:%']);

id
path

2
JP:CA

SELECT 1

id
path

1
NZ:EU

2
JP:CA

SELECT 2

SELECT '%' || unnest(string_to_array('jpca', '')) || '%';

SELECT '%' || regexp_split_to_table('jpca', '') || '%';

SELECT '%' || (regexp_matches('jpca', '.', 'g'))[1] || '%';

?column?

%jpca%

SELECT 1

?column?

%j%

%p%

%c%

%a%

SELECT 4

?column?

%j%

%p%

%c%

%a%

SELECT 4

SELECT *
FROM flights
WHERE path ILIKE ALL (SELECT '%' || regexp_split_to_table('jpca', '') || '%');

id
path

2
JP:CA

SELECT 1