WITH hoge AS (
    SELECT EMP_CODE, 
    EMP_LAST_NAME, 
    EMP_FIRST_NAME
    , COUNT(1) OVER (
        PARTITION BY TRIM(REPLACE(EMP_LAST_NAME, ' ', '')),TRIM(REPLACE(EMP_FIRST_NAME,' ', ''))
        ) AS cc
    FROM EMP
    WHERE VALID_FLG = '1'
)

SELECT DISTINCT
    a.EMP_CODE AS CODE
    , a.EMP_LAST_NAME AS SURNAME
    , a.EMP_FIRST_NAME AS NAME
    
FROM
    hoge AS a INNER JOIN hoge AS b
    ON TRIM(REPLACE(a.EMP_LAST_NAME, ' ', '')) = TRIM(REPLACE(b.EMP_LAST_NAME, ' ', '')) AND
    TRIM(REPLACE(a.EMP_FIRST_NAME,' ', '')) = TRIM(REPLACE(b.EMP_FIRST_NAME,' ', ''))
    AND b.cc > 1
  
ORDER BY TRIM(REPLACE(a.EMP_LAST_NAME, ' ', '')),TRIM(REPLACE(a.EMP_FIRST_NAME,' ', '')),1