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

Question

I am trying to polish up my vocabulary to better communicate with my fellow developers. We have several places in the site where we are debating if we should search for a string from the beginning 'running%' vs anywhere in the string '%running%.

I have been calling the middle search "fuzzy" which I realize is incorrect as fuzzy means changing the form of the word "run", "runing" [sic], "runed" [sic].

What is the correct terminology for searching the beginning of a string and searching the middle of a string?

Answer 1

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

Answer 2

The first thing that comes to mind to me is "un-Sargable." Searching for a specific string, or the first part of a string, in an indexed field allows you to seek. If your search starts with a wildcard, the RDBMS will have to scan the whole index, because values which meet your search predicate could appear anywhere in the set of values.

Consider looking in a telephone book (if you're old enough to remember those...). You can easily find people who's last names start with "Dan:" you thumb to the Ds, flip forward to the DAs, and the DAN-somethings will be all together. If you wanted to find people who's last names include the string "ANIEL," you would have to read every page (scan the table).

Answer 3

It's not really your question, but your example of fuzzy is imprecise.

• Fuzzy is the opposite of sharp, binary, meaning you can have a percentage of match, for instance a fuzzy search for 'run' at precision .5 will include 'ran', 'rud', and a lot of other words. SQL does not support fuzzy search, you need additional systems like Lucene.
• A wildcard search will for 'run%' will always include 'runing' and 'runed', and you can distinguish the begins with and contains ('%run%' to include 'outrunning') as @Solomon Rutzky suggests
• However if you want to find whole words, for instance in text blocks, you would need to indicate the preceeding or trailing whitespace ' run ' (or ' run% ' to include partial matches such as 'bla bla runing bla' and 'bla runed bla bla').