19

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?

  • 1
    I have worked at places that used "Begins with" vs "Contains" to differentiate between those two options. – Solomon Rutzky Oct 4 '17 at 19:41
24

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

1

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).

  • 2
    "RDBMS will have to scan the whole index" that's so not true. postgresql.org/docs/9.6/static/pgtrgm.html – Evan Carroll Oct 4 '17 at 19:00
  • I think unsargable might be a more general term than is being looked for here as it covers a number of other cases (searching the result of a function performed on a column, for example). – David Spillett Oct 6 '17 at 11:50
0

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').

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.