Query Builder: Using Functions

When generating a query you can use any function that MySQL supports. An example:

There are two examples of use of functions here.

The first (and simplest) example is that we no longer have selected just the 'special_features' column of the 'film' table, but a function like 'substr( `special_features`,1,10). This will return a string up to 10 characters starting with the first character of the special_features, but if the `special_features` is longer than 10 characters only the first 10 will be returned. This may make the result look better, and it will be easier to use the result in text and copy it to some kind of document. You will probably recognize the customer from the first 10 characters of his name!

To change from the name of the column itself to a function on that name you just click twice in the cell of the GRID where it displays and edit it. You may even write functions inside other functions and use expressions too like 'truncate(mycolumn,0)+1'. This will (on) return the 'lowest' INTEGER number 'higher' than the actual value DECIMALS.

The second example is a little more complicated but not less important. Instead of filtering `release_year` on the 'year of 2009 and before' we want to filter on the 'year 2009 and  only that year'. We could of course use a '>' and a '<' type of condition and AND those logically, but using the year() function on the `release_year` column would be much more readable. To filter on year() of that column without displaying the year() alone in the result we add a column more showing 'year(release_year)' and UNCHECK the 'SHOW' checkbox for that column. Compare the SQL above with that on the previous page.

This is the method you use whenever you want to sort (ORDER BY ...), filter (WHERE ...) on a column (or a function/expression on a column) without returning the value used for sorting or filtering in the result itself. Actually you can also do the same with the GROUP BY (see next page) so it might look like:

"SELECT a,b,c WHERE x {criteria} ORDER by length(y) ASC GROUP by z;" (in the example y is a string type)

Here all the columns a,b,c,x,y,z are added to the GRID, y is edited using the length() function but only the SHOW checkboxes for the a,b and c columns are checked, so only those will display in the result.

However GROUPING like this (GROUP BY a column  not included in the SELECT clause) is considered 'bad practice' and experts disagree whether such construction is in accordance with SQL standards. With MySQL the situation is that recent versions support this, older versions don't (like most other database servers also don't).

Note: This feature is available in SQLyog Enterprise and Ultimate.