Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH MESSAGE_LOG AS (

SELECT 'KB0192' AS MSG_TYPE_CD, 'ZOHO' AS SRC, '10.247.32.44 | 10.247.32.44' AS SRC_IP FROM dual UNION ALL

SELECT 'KB0192', 'ZOHO', '10.247.32.45 | 10.247.32.45' FROM dual UNION ALL

SELECT 'KB0192', 'ZOHO', '127.0.0.1 | 10.240.20.137' FROM dual UNION ALL

SELECT 'KB0192', 'ZOHO', '127.0.0.1 | 10.240.20.138' FROM dual UNION ALL

SELECT 'KB0196', 'GUPSHUP', '10.240.20.59 | 10.10.1.19' FROM dual

)

SELECT

SRC_IP,

REGEXP_SUBSTR(SRC_IP, '^[^ |]+') AS first_ip

FROM MESSAGE_LOG

ORDER BY

MSG_TYPE_CD;