Last Updated: February 25, 2016

MySQL stored function to normalize User-Agent

I need to normalize User-Agent to offer readability.
Using a function to do so is a good idea if you want to avoid any language dependency if your base is accessed from different environment. It may also help to maintain your code.

Here the function that I use :

DROP FUNCTION IF EXISTS normalize_ua;
DELIMITER $$
CREATE FUNCTION normalize_ua(useragent VARCHAR(500))
 RETURNS VARCHAR(500)
 DETERMINISTIC

BEGIN
 DECLARE normalized_ua VARCHAR(500);
 SELECT CASE
 WHEN useragent LIKE "%iPhone%" AND useragent LIKE "%6_%" THEN 'Iphone 5'
 WHEN useragent LIKE "%iPhone%" AND useragent LIKE "%5_%" THEN 'Iphone 4S'
 WHEN useragent LIKE "%iPhone%" AND useragent LIKE "%4_%" THEN 'Iphone 4'
 WHEN useragent LIKE "%iPhone%" AND useragent LIKE "%3_%" THEN 'Iphone 3S'
 WHEN useragent LIKE "%iPhone%" AND useragent LIKE "%2_%" THEN 'Iphone 3'
 WHEN useragent LIKE "%iPhone%" AND useragent LIKE "%1_%" THEN 'Iphone 1'
 WHEN useragent LIKE "%iPod%" AND useragent LIKE "%6_%" THEN 'Ipod 5G'
 WHEN useragent LIKE "%iPod%" AND useragent LIKE "%4_%" THEN 'Ipod 4G'
 WHEN useragent LIKE "%iPod%" AND useragent LIKE "%3_%" THEN 'Ipod 3G'
 WHEN useragent LIKE "%iPod%" AND useragent LIKE "%2_%" THEN 'Ipod 2G'
 WHEN useragent LIKE "%iPod%" AND useragent LIKE "%1_%" THEN 'Ipod 1G'
 WHEN useragent LIKE "%iPad%" AND useragent LIKE "%6_%" THEN 'Ipod 1G'
 WHEN useragent LIKE "%iPad%" AND useragent LIKE "%6_%" THEN 'Ipad 4'
 WHEN useragent LIKE "%iPad%" AND useragent LIKE "%5_%" THEN 'Ipad 3'
 WHEN useragent LIKE "%iPad%" AND useragent LIKE "%4_%" THEN 'Ipad 2'
 WHEN useragent LIKE "%iPad%" AND useragent LIKE "%3_%" THEN 'Ipad 1'
 ELSE 'Autre'
 END INTO normalized_ua;
 RETURN normalized_ua;
END
$$
DELIMITER ;

#mysql

#function

#user-agent

#procedure