Question

I want to return a query that returns film titles that contain letter 'a' followed by repeating characters from the table below.

For example, Married Go (a film in the table) has repeating characters following the letter a.

Table:

CREATE TABLE film_cat_rate (
category character varying(25),
title character varying(255),
rental_rate numeric(4,2)
);

INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('New', 'Amistad Midsummer', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Drama', 'Apollo Teen', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Family', 'Bedazzled Married', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Family', 'Blood Argonauts', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Sports', 'California Birds', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Sci-Fi', 'Camelot Vacation', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Sports', 'Caribbean Liberty', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Animation', 'Champion Flatliners', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Drama', 'Conquerer Nuts', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Documentary', 'Dozen Lion', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Classics', 'Dracula Crystal', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Music', 'Elf Murder', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Foreign', 'Fiction Christmas', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Animation', 'Fight Jawbreaker', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Comedy', 'Freedom Cleopatra', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Travel', 'Games Bowfinger', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Foreign', 'Gentlemen Stage', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Action', 'Gosford Donnie', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Games', 'Grinch Massage', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Action', 'Handicap Boondock', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Documentary', 'Hardly Robbers', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Foreign', 'Hoosiers Birdcage', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Documentary', 'Hunter Alter', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Documentary', 'Independence Hotel', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Travel', 'Italian African', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Classics', 'Jerk Paycheck', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Children', 'Jumping Wrath', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Classics', 'League Hellfighters', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Foreign', 'Lost Bird', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Sci-Fi', 'Married Go', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Action', 'Midnight Westward', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Horror', 'Motions Details', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Family', 'Music Boondock', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Documentary', 'North Tequila', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Animation', 'Oz Liaisons', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Games', 'Panky Submarine', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Foreign', 'Pearl Destiny', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Games', 'Private Drop', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Action', 'Quest Mussolini', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Children', 'Sabrina Midnight', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('New', 'Salute Apollo', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Sports', 'Saturday Lambs', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Children', 'Scarface Bang', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Drama', 'Something Duck', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Travel', 'Tomatoes Hellfighters', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Foreign', 'Town Ark', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Children', 'Uptown Young', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Sci-Fi', 'Vacation Boondock', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Foreign', 'Vision Torque', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Animation', 'Wait Cider', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Drama', 'Wardrobe Phantom', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Sci-Fi', 'Weekend Personal', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Foreign', 'Whale Bikini', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Documentary', 'Wife Turn', 4.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Family', 'Willow Tracy', 2.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Comedy', 'Wisdom Worker', 0.99);
INSERT INTO film_cat_rate (category, title, rental_rate) VALUES ('Music', 'Won Dares', 2.99);

Below is what I have, but I am returning no output. I am using postgres.

select regexp_matches(title, '.{2,}+')
from film_cat_rate
where title like '%a%';

Answer 1

There is no any need of regexp_matches(title, '.{2,}+') to print title contain letter 'a' followed by repeating characters. It only done by  like '%a%'.

Only LIKE operator can perform this, there is no any need of regex.

Thus, below query returns all the title containing letter 'a' followed by repeating characters.

SELECT title FROM film_cat_rate
WHERE title LIKE '%a%';

Output of above query :

NOTE : I used SSMS to run the query