Mysql 根据URL获取顶级域名

CREATE DEFINER=`root`@`%` FUNCTION `fn_get_topDomainName_from_url`(`Article_URL_In` varchar(1000)) RETURNS varchar(200) CHARSET utf8
BEGIN
  /***********************************************************
     根据URL获取顶级域名
  ************************************************************/
  declare domain_name_out varchar(200);
  declare domain_info varchar(200);
  declare root_domain_name varchar(200);
  
  -- 获取域名信息
  SET domain_info=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(Article_URL_In, '/', 3), '/', -1) ,':',1);

  -- 提取域名中的根域
  SET root_domain_name=(IF(domain_info REGEXP '(com|tel|mobi|net|org|asia|me|tv|biz|cc|name|info|gov|cn|co|edu|europa)(\\.[a-zA-Z]{2})$', 
      SUBSTRING_INDEX(domain_info, '.', -2), 
      IF(domain_info REGEXP '([a-zA-Z]{2,4})$', SUBSTRING_INDEX(domain_info, '.', -1), '')));

  -- 根据子域名和根域取一级域名 
  SET domain_name_out=CONCAT(SUBSTRING_INDEX(TRIM(TRAILING root_domain_name FROM domain_info), '.', -2), root_domain_name); 
  
  return domain_name_Out;
END

转载于:https://www.cnblogs.com/krisy/archive/2012/12/10/2811124.html