Sometimes you are working with a PostgreSQL database with A LOT of tables and looking for tables that contain a sub-string in their name. Following is a query that you can run that will return all of the tables that have the string in their name:
table_name LIKE '%<string>%'
AND table_schema not in ('information_schema', 'pg_catalog')
AND table_type = 'BASE TABLE'