SELECT 
	MEMBER_CODE as MEMBER
	,EMAIL as EMAIL
	,replace(substring(substring
	    (EMAIL
	    , INSTR(EMAIL,'.co.jp')
	    ,-100
	    )
    ,INSTR(
        substring
	    (EMAIL
	    , INSTR(EMAIL,'.co.jp')
	    ,-100
	    ),'@'
        
    ),100
    ),'@','') as DOMAIN
    
FROM MEMBER_MST
ORDER BY 
	DOMAIN desc
	,MEMBER desc
;