While it is possible to use expressions in the search bar, the need does not arise very often so perhaps we'll go there later. Expression functions can be used in lots of places around MC, for different reasons, with the most common reason being aesthetic, so lets start there.
Say you want a pane to show you the [number plays] field. Add one to a view scheme and you see the values for [number plays], but, all those files that have never been played are listed in the pane under "Unassigned". I don't like unassigned, and now that we have expressions, we can get rid by using one instead of the standard library field. Simply get rid of the standard [number plays] pane, and replace it with an expression that says, show me [number plays], unless it's empty, in which case, show me "Not Played Yet" if(isempty([number plays],Not Played Yet,[number plays])
I use the formatdate expression quite a lot to get dates shown the way I want them. [date imported] without the time, for example.
formatdate([date imported,0],yyyy - MM -dd)
Where these things really come into their own, is with your picture library. The difference is that with audio, everything is pretty linear, and using a details view, most of the info we want is there to see, easy. With pictures, we really need to be in thubnail view, so detais are hidden, and space beneath the thumbnail for information is tight. Enter expressions... My image workflow dictates that if a picture does not have a subject tag, then it's not tagged, so, the following expression:
will place the not tagged text under the thumbnail if it's not tagged, and show nothing if it is tagged, freeing up space for other info I may want on display.
In this post, I describe how creating two new expression based library fields from the [date (filename friendly)] field and then sorting by them, allows you to list photos "Newest first, but sorted oldest first within days" so you see photos taken today at the top of the list, sorted into the order they were actually taken.
When using the "rename from properties" tool, I use strict rules, dictated by media type. I've created rather long expressions for both the file name, and the directory fields which allow me to select a bunch of tagged files, right click, rename from properties and immediately click the OK button. I don't need to worry myself about things like [media type], if the tags are correct, MC will place each file in the correct location. One of the many improvements introduced in v12 is the ability to use expressions in the 'base path' field too. This means that we can now use an expression here to do things such as place different file types on different drives.
When playing picture slideshows, MC shows a caption beneath each slide. This is nice, but a pain if pictures don't all contain the tags specified in the caption string. Again, this can be avoided by using expressions to display a different tag if another is not available.
If you use the playingnow plugin, you'll know that it displays tag info in a popup box. For the most part, that info is static, but, you can create expression based fields specifically for playingnow to use. In my signature image, the text in the bottom line, from the number of plays to the end is all generated by a single expression based field.
In summary, expressions are a lot like smartlists in that they are more personal than portable. You would use them to bend MC into a shape that's right for you, for reasons best known to you, therefore, your expressions would likely not make a lot of sense, nor be a lot of use to someone else, hence the reason, IMVHO, that the expression and smartlist sharing threads are not as popular as everyone wishes they were.
When you want MC to do something, best policy so far has been to ask how. If expressions are the answer, take that opportunity to ask why, and so learn a little from the questions. Once you get started, it doesn't take long before you instinctively know what you need, and how to get it. continued (AND, OR, and XOR)
Following is a collection of Database Expressions that users have contributed.