MySQL – stored function example

[adsense id=”0514458240″ width=”468″ height=”60″]

I have been asked once how to get the max version number which is stored in the format: xx.yy.zz. However, the information stored did not include leading zeros. This caused a problem as follows:

mysql> select version from software;
+--------+
| version   |
+--------+
| 2.1.9  |
| 2.1.10 |
+--------+
2 rows in set (0.00 sec)
mysql> select max(version) from software;
+-----------+
| max(version) |
+-----------+
| 2.1.9     |
+-----------+
1 row in set (0.00 sec)

In the example above, 2.1.9 has a higher value than the 2.1.10 which clearly is the higher of the two.

This can only be solved using a stored function as follows:

DROP FUNCTION IF EXISTS pversion;
delimiter $$
CREATE FUNCTION pversion(p_ver VARCHAR(20))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
	DECLARE l_start INT;
	DECLARE l_end INT;
	DECLARE l_maj VARCHAR(5);
	DECLARE l_med VARCHAR(5);
	DECLARE l_min VARCHAR(5);

	SET l_start = 1;
	SET l_end = LOCATE('.', p_ver, l_start);
	IF (l_start + 1) = l_end THEN
		SET l_maj = CONCAT('0',SUBSTR(p_ver, l_start, 1));
	ELSE
		SET l_maj = SUBSTR(p_ver, l_start, 2);
	END IF;
	SET l_start = l_end+1;
	SET l_end = LOCATE('.', p_ver, l_start);
	IF (l_start + 1) = l_end THEN
		SET l_med = CONCAT('0',SUBSTR(p_ver, l_start, 1));
	ELSE
		SET l_med = SUBSTR(p_ver, l_start, 2);
	END IF;
	SET l_start = l_end+1;
	SET l_end = LOCATE('.', p_ver, l_start);
	IF LENGTH(SUBSTR(p_ver, l_start)) = 1 THEN
		SET l_min = CONCAT('0',SUBSTR(p_ver, l_start));
	ELSE
		SET l_min = SUBSTR(p_ver, l_start);
	END IF;
	RETURN CONCAT(l_maj,'.',l_med,'.',l_min);

END;
$$
delimiter ;

Now if we attempt to access the information using this function:

mysql> select max(pversion(version)) from software;
+---------------------+
| max(pversion(version)) |
+---------------------+
| 02.01.10            |
+---------------------+
1 row in set (0.00 sec)