CodePuzzling::tumblr('!');

[MySQL] How to clean URL with a function

Suppose that you have a table with a column named “referer” which stores referring urls and suppose that you want to update this column by cutting off everything but the domain and subdomain. Here it is a small function that does the job:

delimiter //
drop function if exists sub_domain;
CREATE FUNCTION sub_domain (url text(1000)) RETURNS CHAR(100)
BEGIN
DECLARE str1 varchar(10);
DECLARE str2 varchar(10);
SET str1="http://";
SET str2="https://";
RETURN if(substring_index(substring_index( substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-1) != 'com', substring_index( substring_index( substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-3), substring_index( substring_index( substring_index(REPLACE(url,str2,str1), '/',3),str1,-1),'.',-3));
END;
//

UPDATE `mytable` 
SET `referer` = (SELECT sub_domain(`referer`));