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 ;
Written by pmaoui
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Mysql
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#