admin – Page 85 – IT Integrated Business Solutions

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

Today I responded to someone that had a problem with sorting their data. Unfortunately their data model was the problem more than the data. However, I thought I would share with you the problem and the resulting code that I generated for them.

The issue is that an apparel system included a field that contained both the apparel model name and a particular style. For instance, jeans01white. The format of the data always included the model name, a number and finally the style. However, the model name may include numbers. As a result, the person was looking for a way of getting only the model from this.

The algorithm is simple, start at the end and continue scanning each character until a number is found. Then take the substring from the first character until and including this first numeric. This is not possible (or complex) in a single query. As a result a stored function can help with this processing:

--
-- Ronan Cashell (c) IT Integrated Business Solutions 2011
-- 01 March 2011
-- https://it-iss.com
--
DROP FUNCTION IF EXISTS getProduct;

DELIMITER $$
CREATE FUNCTION getProduct(aProduct VARCHAR(30))
RETURNS VARCHAR(30)
NO SQL
BEGIN
	DECLARE count INT;
	DECLARE i INT;
	DECLARE prod VARCHAR(30);
	SET prod = aProduct;
	SET count = LENGTH(aProduct);
	SET i = 0;
	WHILE i < count AND (SUBSTRING(prod, count-i, 1) < '0' OR SUBSTRING(prod, count-i, 1) > '9') DO
		SET i = i + 1;
	END WHILE;
	RETURN LEFT(prod, count-i);
END;
$$

DELIMITER ;

In action a table contains the following product numbers and the resulting output using this function:

mysql> select getProduct(product) as model, product from c;+-----------------------+--------------------------+
| model                 | product                  |
+-----------------------+--------------------------+
| A01                   | A01black                 | 
| A01                   | A01white                 | 
| A02                   | A02yellow                | 
| A03                   | A03blue                  | 
| A03                   | A03grey                  | 
| A09                   | A09green                 | 
| clothing-dress-y-5255 | clothing-dress-y-5255red | 
+-----------------------+--------------------------+
7 rows in set (0.01 sec)

Author: admin

MySQL – stored function example