MYSQL通过SQL语句提取表中URL数据的域名

2024-03-05 11:54| 来源: 网络整理| 查看: 265

MYSQL通过SQL语句提取表中URL数据的域名：

举例：

https://www.baidu.com/s?ie=UTF-8&wd=123 > www.baidu.com

http://www.baidu.com/s?ie=UTF-8&wd=123 > www.baidu.com

select SUBSTR((REPLACE(REPLACE('https://www.baidu.com/s?ie=UTF-8&wd=123','https://',''),'http://','')), 1, INSTR((REPLACE(REPLACE('https://www.baidu.com/s?ie=UTF-8&wd=123','https://',''),'http://','')),'/')-1)

如果做统计：

select SUBSTR((REPLACE(REPLACE(url,'https://',''),'http://','')), 1, INSTR((REPLACE(REPLACE(url,'https://',''),'http://','')),'/')-1) host1, count(1) from dbo.table1 group by SUBSTR((REPLACE(REPLACE(url,'https://',''),'http://','')), 1, INSTR((REPLACE(REPLACE(url,'https://',''),'http://','')),'/')-1) order by count(1) desc

【本文地址】

公司简介

联系我们