求一sql函数，求字符串截取,该如何解决

求一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