Postgres 14

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

CREATE TABLE tbl (tbl_id int, string text);

INSERT INTO tbl VALUES
  (1, 'a really nice application')
, (2, 'apples are cool')
, (3, 'appliances')
, (4, 'old oranges do not match')
, (5, 'apple has boring applications to apply')
, (6, 'apple apple')
, (7, 'dapple does not match')
;

CREATE TABLE



INSERT 0 7


-- FTS prefix matching
SELECT * FROM tbl
WHERE  to_tsvector('simple', string) @@ to_tsquery('simple', 'appl:*');


tbl_id
string


1
a really nice application

2
apples are cool

3
appliances

5
apple has boring applications to apply

6
apple apple



SELECT 5


-- regexp
SELECT *
FROM   tbl
WHERE  string ~ '\mappl';


tbl_id
string


1
a really nice application

2
apples are cool

3
appliances

5
apple has boring applications to apply

6
apple apple



SELECT 5


-- Order by count of matches with regexp_matches()
SELECT tbl_id, string, count(*) AS matches
FROM  (
   SELECT tbl_id, string, regexp_matches(string, '\mappl', 'g')
   FROM   tbl
   WHERE  string ~ '\mappl'
   ) sub
GROUP  BY 1, 2
ORDER  BY 3 DESC, 2, 1;


tbl_id
string
matches


5
apple has boring applications to apply
3

6
apple apple
2

2
apples are cool
1

3
appliances
1

1
a really nice application
1



SELECT 5


-- Order by count of matches with regexp_split_to_table():
SELECT tbl_id, string, count(*) - 1 AS matches
FROM  (
   SELECT tbl_id, string, regexp_split_to_table(string, '\mappl')
   FROM   tbl
   WHERE  string ~ '\mappl'
   ) sub
GROUP  BY 1, 2
ORDER  BY 3 DESC, 2, 1;


tbl_id
string
matches


5
apple has boring applications to apply
3

6
apple apple
2

2
apples are cool
1

3
appliances
1

1
a really nice application
1



SELECT 5

tbl_id	string
1	a really nice application
2	apples are cool
3	appliances
5	apple has boring applications to apply
6	apple apple