select
    MEMBER_CODE as MEMBER
    ,EMAIL as EMAIL
    ,replace(
        substr(EMAIL, 
        instr(EMAIL, '@') + 1, 
        length(EMAIL) - instr(EMAIL, '@')
        ), '.co.jp', ''
        ) as DOMAIN
from MEMBER_MST
order by
    DOMAIN desc
    ,MEMBER desc
;