What is it called when you search the middle of a string instead of the beginning?

It's called an "un-anchored search pattern", and it looks like this in SQL.

foo LIKE '%bar%'

If you lack a % on either side, it is said that the search pattern anchors to the start or end of the string respectively. This lingo comes from the regex world.

foo LIKE 'bar%'

You would say, "the search pattern bar% anchored to the start of the string".

For comparison, a PCRE is anchored with ^ or $ tokens and it looks like ^bar or bar$. PCREs require explicit anchoring with tokens, whereas SQL LIKE statements are implicitly anchored and require explicit % to create an "un-anchored search pattern".

As a side note, you can index these types of expressions with trigrams using something like pg_trgm in PostgreSQL