otgv1a
Last Updated: February 25, 2016
·
1.185K
· poupougnac

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 ;