Yes, use Like. A query such as:
Select id from sometable where name like '%abc%'
would return any row that contained “abc” anywhere in the name column.
If the pattern you are looking for happens to contain the % or _ character, you can use the ESCAPE keyword to define an escape character to include that special character in the expression. To look for the string “somename%” (including the %), it’d look something like:
select id from mytable where name like '%somename\%%' escape '\'