求一sql函数，求字符串截取
表
http://www.xx.com/
http://a.b.c.com/
http://a.x.com.cn/
http://a.c.net.cn/
http://a.w.net/
http://a.s.cn/
如上表所示，每一条记录根据.拆分，从后向前，直到不等于com/cn/net结束并返回
xx
c
x
c
w
s
大家能明白我的意思吧，就是求网址的最主要那部分，不要最后的域名，也不要前面的二级域名、三级域名等等，求一函数。
[解决办法]
- SQL code
select s= right(a, charindex('.',REVERSE(a))-1 ) from ( select a= left(a, (case when charindex('.com', a)>0 then charindex('.com', a)-1 when charindex('.net', a)>0 then charindex('.net', a)-1 when charindex('.cn', a)>0 then charindex('.cn', a)-1 else len(a) end )) from ( select a = replace(replace(a,'http://',''),'/','') from ( select a='http://www.xx.com/' union all select a='http://a.b.c.com/' union all select a='http://a.x.com.cn/' union all select a='http://a.c.net.cn/' union all select a='http://a.w.net/' union all select a='http://a.s.cn/' ) t0 ) t1) t2
[解决办法]
- SQL code
DROP TABLE tbeCREATE TABLE tbe( url VARCHAR(100))GOINSERT INTO tbeSELECT 'http://www.xx.com/' UNIONSELECT 'http://a.b.c.com/' UNIONSELECT 'http://a.x.com.cn/' UNIONSELECT 'http://a.c.net.cn/' UNIONSELECT 'http://a.w.net/' UNIONSELECT 'http://a.s.cn/'IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'suburl')BEGIN DROP FUNCTION suburlENDGOCREATE FUNCTION suburl (@URL VARCHAR(100))RETURNS VARCHAR(100)ASBEGINDECLARE @Suburl VARCHAR(100)IF CHARINDEX('.COM',@URL) > 0BEGIN SET @Suburl = LEFT(@URL,CHARINDEX('.COM',@URL) - 1) WHILE CHARINDEX('.',@Suburl) > 0 BEGIN SET @Suburl = RIGHT(@Suburl,LEN(@Suburl) - CHARINDEX('.',@Suburl)) ENDENDELSE IF CHARINDEX('.NET',@URL) > 0BEGIN SET @Suburl = LEFT(@URL,CHARINDEX('.NET',@URL) - 1) WHILE CHARINDEX('.',@Suburl) > 0 BEGIN SET @Suburl = RIGHT(@Suburl,LEN(@Suburl) - CHARINDEX('.',@Suburl)) ENDENDELSE IF CHARINDEX('.CN',@URL) > 0BEGIN SET @Suburl = LEFT(@URL,CHARINDEX('.CN',@URL) - 1) WHILE CHARINDEX('.',@Suburl) > 0 BEGIN SET @Suburl = RIGHT(@Suburl,LEN(@Suburl) - CHARINDEX('.',@Suburl)) ENDENDRETURN @SuburlENDGOSELECT DBO.suburl(URL)FROM tbe(No column name)ccswxxx