By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE odi_ods_partner_dim (PARTY_ID, PT_PARTNER_ID, PT_PARTNER_EMAIL) AS
SELECT '3223218102E17', 10101363, 'DEEPAKSKORI@EXAMPLE.COM,,ALL' FROM DUAL UNION ALL
SELECT '5951118102E17', 12059043, 'jalaj79@example.com,,' FROM DUAL UNION ALL
SELECT '1113418102E17', 12059044, 'hassanselim1993@example.com,,' FROM DUAL UNION ALL
SELECT '2579321752017', 36419244, 'drshetty@example.co.in,,' FROM DUAL UNION ALL
SELECT 'ABC1234567890', 12345678, 'example@example.com' FROM DUAL;
5 rows affected
SELECT party_id,
pt_partner_id,
pt_partner_email,
UPPER(
CASE INSTR(pt_partner_email, ',,')
WHEN 0
THEN pt_partner_email
ELSE SUBSTR(pt_partner_email, 1, INSTR(pt_partner_email, ',,') - 1)
END
) AS valid_email_id
FROM odi_ods_partner_dim
WHERE LENGTH(pt_partner_email) > 3;
| PARTY_ID | PT_PARTNER_ID | PT_PARTNER_EMAIL | VALID_EMAIL_ID |
|---|---|---|---|
| 3223218102E17 | 10101363 | DEEPAKSKORI@EXAMPLE.COM,,ALL | DEEPAKSKORI@EXAMPLE.COM |
| 5951118102E17 | 12059043 | jalaj79@example.com,, | JALAJ79@EXAMPLE.COM |
| 1113418102E17 | 12059044 | hassanselim1993@example.com,, | HASSANSELIM1993@EXAMPLE.COM |
| 2579321752017 | 36419244 | drshetty@example.co.in,, | DRSHETTY@EXAMPLE.CO.IN |
| ABC1234567890 | 12345678 | example@example.com | EXAMPLE@EXAMPLE.COM |