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 tbe
CREATE TABLE tbe
(
    url VARCHAR(100)
)
GO
INSERT INTO tbe
SELECT 'http://www.xx.com/' UNION
SELECT 'http://a.b.c.com/' UNION
SELECT 'http://a.x.com.cn/' UNION
SELECT 'http://a.c.net.cn/' UNION
SELECT 'http://a.w.net/' UNION
SELECT 'http://a.s.cn/'

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'suburl')
BEGIN
    DROP FUNCTION suburl
END
GO

CREATE FUNCTION suburl (@URL VARCHAR(100))
RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @Suburl VARCHAR(100)

IF CHARINDEX('.COM',@URL) > 0
BEGIN
    SET @Suburl = LEFT(@URL,CHARINDEX('.COM',@URL) - 1)
    WHILE CHARINDEX('.',@Suburl) > 0
    BEGIN
        SET @Suburl = RIGHT(@Suburl,LEN(@Suburl) - CHARINDEX('.',@Suburl))
    END
END
ELSE IF CHARINDEX('.NET',@URL) > 0
BEGIN
    SET @Suburl = LEFT(@URL,CHARINDEX('.NET',@URL) - 1)
    WHILE CHARINDEX('.',@Suburl) > 0
    BEGIN
        SET @Suburl = RIGHT(@Suburl,LEN(@Suburl) - CHARINDEX('.',@Suburl))
    END
END
ELSE IF CHARINDEX('.CN',@URL) > 0
BEGIN
    SET @Suburl = LEFT(@URL,CHARINDEX('.CN',@URL) - 1)
    WHILE CHARINDEX('.',@Suburl) > 0
    BEGIN
        SET @Suburl = RIGHT(@Suburl,LEN(@Suburl) - CHARINDEX('.',@Suburl))
    END
END



RETURN @Suburl

END

GO

SELECT DBO.suburl(URL)
FROM tbe

(No column name)
c
c
s
w
x
xx