Flights table:

SELECT
    path
FROM
    flights
WHERE
    path ILIKE '%' || 'jpca' || '%'

Above query isn't working, it needs to return second record. But it's working if I provide for example:

• jp
• ca
• p
• jp:ca
• CA

It should also work for

• jpca
• pj
• cp
• a:p

Edited: Regex answer is also accepted.

CodePudding user response：

It's much easier if you process the column path before the match:

SELECT path
FROM   flights
WHERE  lower(regexp_replace(path, '[^a-zA-Z]', '', 'g')) ~ lower('JPCA');

This removes all characters except A-Z and a-z and casts the result to lower case before trying a regular expression match. Related:

• Extract numbers from a field in PostgreSQL
• LOWER LIKE vs iLIKE

If your table is big and you need it to be fast, create a trigram expression index:

CREATE INDEX flights_path_expr_idx ON flights
USING gin (lower(regexp_replace(path, '[^a-zA-Z]', '', 'g') gin_trgm_ops);

Requires the additional module pg_trgm installed. See:

• PostgreSQL LIKE query performance variations

Or add a "generated column" to your table and a plain B-tree index on that:

ALTER TABLE flights 
  ADD COLUMN path_ascii text GENERATED ALWAYS AS (lower(regexp_replace(path, '[^a-zA-Z]', '', 'g'))) STORED;

CREATE INDEX flights_path_ascii_trgm_idx ON flights USING gin (path_ascii gin_trgm_ops);

Then:

SELECT path FROM flights WHERE path_ascii ~ 'jpca';

See:

• Computed / calculated / virtual / derived columns in PostgreSQL

CodePudding user response：

If you don't care for the characters of your term to occur in exactly that sequence, but only want to specify a search that matches each of these characters somewhere, you can use the ALL keyword to match multiple ILIKE patterns at once:

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

Now to generate that array from just a single string, you can use

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