For example I have a table named "location" and a column "name". I want all names that contain the sub-string "abe" for example. I am using postegresql.
As you mention postgresql in your tag you should have a look at the PostgreSQL documentation.
There is a topic about pattern matching
One solution for your query would be:
where name like '%abe%'
LIKE is also explained on w3schools.