The Finder searches for word prefixes.
If you save your search as a Smart Folder and then inspect that using a text editor (e.g. TextMate) you will see the raw Spotlight query:
((kMDItemDisplayName = "foo*"cdw))
The "w" modifier tells Spotlight to search for words. The "*" wildcard at the end of the search string makes Spotlight search for word prefixes. It will match names that contain a word beginning with "foo".
That matches all of your examples but one. "foo" comes at the start of the file name, after a space or punctuation or is capitalized. The exception is "snafoos" where "foo" is just a sequence of letters in the middle of the word.
You could change the query in the smart folder to:
((kMDItemDisplayName = "*foo*"cd))
Or click the "+" button below the search field in Finder and add a "name" "contains" "foo" criterion.
Or use mdfind in Terminal:
mdfind -onlyin . '((kMDItemDisplayName = "*foo*"cd))'
Or use HoudahSpot (of which I am the developer) to get more control over your search.