Enhance your Queries with Stored Functions

July 24, 2009

HeidiSQL 4’s Stored Routine Editor offers a user-friendly alternative to using a command-line interface to create and manage your stored procedures and functions. In the last article, we covered how to write a stored procedure using the Stored Routine Editor. Today, we’ll be learning how to take advantage of some useful native MySQL functions as well as use the editor to create our own custom functions.

Downloading HeidiSQL

If you haven’t already done so, visit the HeidiSQL website, download version 4 and install it, following their instructions.

Launch HeidiSQL to display the Session Manager:

For our purposes you can use the default MySQL session and user account, just to keep things simple. Hit the Connect button to open the main application window.

Create the Help Database

Download the help database.

To import the database tables:

That will open the file in a new Query editor window.

If you expand the help database, you’ll see that it contains four tables:

Some Useful MySQL Functions

Before going off and whipping up your own custom functions, be sure to check if what you need is already covered by the many excellent native functions supported by MySQL. It’s always better to use these first, as they are already tested for you and require very little effort to insert in your SQL statements or stored procs. Here are some standouts:

Concat

The CONCAT function is used to concatenate two or more strings to form a single string. Its signature is simple:

CONCAT(str1,str2,...);

Here’s an example that produces a user ID:

SELECT CONCAT(name, dob_year, '_999') as ID
FROM user_data;

Produces:

Rick1978_999
Jaimie1980_999
Sandra1967_999

CONCAT_WS()

CONCAT_WS stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated:

CONCAT_WS(separator,str1,str2,...)

One good use for it is for formatting names. For example, it is common to display names starting with the last name. Rather than send both the first and last names as separate fields, we can do the work in one fell swoop by using the CONCAT_WS function:

SELECT CONCAT_WS(', ', last_name, first_name) AS name
FROM CLIENTS

Would return something like:

Gravelle, Rob
Perinbam, John
Nesrallah, George
Mercer, Steve

LOWER(str), UPPER(str)

Returns the string with all characters changed to lower or upper case according to the current character set mapping:

LOWER(str); / UPPER(str);

These functions are useful when comparing strings whose case is uncertain. For instance, say we wanted to retrieve business commands from a table that match the 'SEND' command. However, we are unsure that commands are all in uppercase. We can use the UPPER function to convert the contents of that field when making the comparison:

SELECT * 
FROM commands
WHERE UPPER(cmd_desc) = 'SEND';

LTRIM(str), RTRIM(str), TRIM(str)

Returns the string str with either the leading space (LTrim), trailing space (RTrim), or both (Trim) characters removed. The latter, Trim function offers by far the most flexibility in that, not only can it perform the same actions as the LTrim and Rtrim functions, but it can remove other characters besides spaces. Here are two alternate signatures for the Trim function:

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

TRIM([remstr FROM] str)

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

EXAMPLE 1 – LTRIM:

SELECT LTRIM('   Doheny Dr.');

Returns:

'Doheny Dr.'

EXAMPLE 2 – RTRIM:

SELECT RTRIM(' Michigan State    ');

Returns:

' Michigan State'

EXAMPLE 3 – TRIM:

SELECT TRIM('   No spaces for me.  ');

Returns:

'No spaces for me.'


SELECT TRIM(LEADING 'x' FROM 'xxx22 Acacia Ave.xxx');

Returns:

'22 Acacia Ave.xxx'


SELECT TRIM(BOTH 'x' FROM 'xxx22 Acacia Ave.xxx');

Returns:

'22 Acacia Ave.'

IN

You can use IN function to replace many OR conditions.

IN(arg1, arg2, arg3, ...)

SELECT *
FROM   accounts_t 
WHERE  account_no in ( 199, 205, 300, 506, 700 ); 

Replaces:

SELECT *
FROM   accounts_t 
WHERE  account_no = 199
OR     account_no = 205
OR     account_no = 300
OR     account_no = 506
OR     account_no = 700; 

The following select statement retrieves descriptions from a table where the IDs match those found in an unrelated table, where foreign key linking is not possible. The nested select returns a list of IDs from the first table with the description of ‘site poe’. Then the IN function is used to match the IDs of a second table to the returned ID list:

SELECT desc 
FROM   vcode_values 
WHERE  code_value_id IN (select  code_value_id
                      	 from    vcode_table_data
                         where   LOWER(description) = 'site poe')

ROUND(X,[D])

This function returns X rounded to the nearest integer. If a second argument, D, is supplied, then the function returns X rounded to D decimal places. D must be positive or all digits to the right of the decimal point will be removed. Here’s some examples:

SELECT ROUND(5.693893);

Returns:

5


SELECT ROUND(5.693893, 2);

Returns:

5.69

Creating Custom Functions Using the Stored Routine Editor

Custom functions are a great way to group common calculations and expressions together. As with programming languages, functions help reduce redundant code and improve readability. It’s easy to create your own function is HeidiSQL using the Stored Routine Editor:

BEGIN
  DECLARE isEmpty TINYINT;
  SET isEmpty =(param_name IS NULL or char_length(TRIM(param_name))=0);
  RETURN isEmpty;
END

Here is the Stored routine editor with all the fields filled in:

This function determines whether or not a parameter was supplied to a stored procedure. The isEmpty variable is declared as a tynyint, set to an expression that uses the IS NULL clause and two native functions to check for empty strings, and returned from the function.

Our new function can now be called from SQL statements like any other function:

IF isEmpty(keyword) THEN
  #code not shown
ELSEIF isEmpty(topic) THEN
  #more code

That concludes our series on HeidiSQL Stored procedures editor. From here on in, we’ll be getting back to MySQL and general database topics.

» See All Articles by Columnist Rob Gravelle