sql求网址的域名,该怎么解决

sql求网址的域名
表tb中有http字段，内容为
http://www.abc.com/
http://www.a.cn/1.htm
http://e.s.d.gov/1/2/
http://f.w.d.gov/
http://e.c.a.com/
null
空
...等等内容
想求域名出现的次数(二级域名求一级域名)

我在用CHARINDEX('.c', http)时，发现当二级域名中含有.c时，求得的结果不对，后来打算用
select 
http, 
substring(http, 1, PATINDEX('%.com[].cn[].cc[]%', http)-1)
from tb1
但是不好使，请问怎么修改？

[解决办法]

SQL code

--> 测试数据：#if object_id('tempdb.dbo.#') is not null drop table #create table #(src varchar(21))insert into #select 'http://www.abc.com/' union allselect 'http://www.a.cn/1.htm' union allselect 'http://e.s.d.gov/1/2/' union allselect 'http://f.w.d.gov/' union allselect 'http://e.c.a.com/' union allselect null union allselect '空'select *, left( src, charindex('/', src, charindex('//',src)+2) )host from # where src like '%://%'/*src                   host--------------------- ---------------------http://www.abc.com/   http://www.abc.com/http://www.a.cn/1.htm http://www.a.cn/http://e.s.d.gov/1/2/ http://e.s.d.gov/http://f.w.d.gov/     http://f.w.d.gov/http://e.c.a.com/     http://e.c.a.com/*/
[解决办法]
SQL code
declare @url nvarchar(30);declare @index int;set @url='http://baidu.com/hello/world/a.html';select SUBSTRING(@url,0,(CharIndex('/',replace(@url,'http://',''))+7))------------------------------http://baidu.com(1 行受影响)