Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Jul 24, 2009

Enhance your Queries with Stored Functions

By Rob Gravelle

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:

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:

1.  From the Import menu, click the “Load SQL File...” item and select the Help.sql file in the “Open” dialog:

click the “Load SQL File...”

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

2.  Click the blue arrow button in the toolbar to execute the query (not the Query tab icon!). It’ll take a minute or two for the database to be added and the tables populated with data. Once the app finishes, you may have to refresh the database tree pane on the left-hand side by right-clicking it and selecting “Refresh” from the popup menu. Alternatively you can just hit the F5 key.

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

the database 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:

1.  Bring up the editor by right-clicking the help database icon and selecting “Create stored routine ...” from the popup menu:

Bring up the editor

2.  Enter “isEmpty” in the Name field.

3.  Select “Function” from the Type list.

4.  Select “Tinyint(1)” from the Returns list.

5.  Select “No SQL” from the Data access list.

6.  Leave the SQL Security as “Definer”.

7.  Add a parameter called “param_name” with a Datatype of “VARCHAR(25) ” (don’t forget the space at the end). Note that the Context cannot be changed from “IN” for functions.

8.  Add the following code in the Routine body:

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:

the Stored routine editor

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.

9.  Click OK to close the dialog and create 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



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM