'Oracle db - remove ,, from email address

I'm running below code and getting below results but I want to remove ,, and after ,, texts from email address and that email address should show in UPPER(PT_PARTNER_EMAIL) VALID_EMAIL_ID column. How can I achieve this?

select party_id, pt_partner_id, pt_partner_email, upper(pt_partner_email) 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    [email protected],,ALL     [email protected],,ALL
5951118102E17   12059043    [email protected],,            [email protected],,
1113418102E17   12059044    [email protected],,    [email protected],,
2579321752017   36419244    [email protected],,     [email protected],,
sqloracleplsql


Solution 1:[1]

please suggest how can I achive this.

try using these

  1. Upper - Make all to upper case https://www.techonthenet.com/oracle/functions/upper.php
  2. Instr - find the position of ,, https://www.techonthenet.com/oracle/functions/instr.php
  3. Substr - Cut from start to where ,, is https://www.techonthenet.com/oracle/functions/substr.php

e.g.

select upper( substr('wo++' , 1   ,  instr('wo++' , '++' )-1 ) )  from dual
--result WO

Solution 2:[2]

You can check if the ,, substring exists using the INSTR function and then, if it does, remove it using SUBSTR and INSTR:

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

Which, for the sample data:

CREATE TABLE odi_ods_partner_dim (PARTY_ID, PT_PARTNER_ID, PT_PARTNER_EMAIL) AS
SELECT '3223218102E17', 10101363, '[email protected],,ALL'  FROM DUAL UNION ALL
SELECT '5951118102E17', 12059043, '[email protected],,'         FROM DUAL UNION ALL
SELECT '1113418102E17', 12059044, '[email protected],,' FROM DUAL UNION ALL
SELECT '2579321752017', 36419244, '[email protected],,'      FROM DUAL UNION ALL
SELECT 'ABC1234567890', 12345678, '[email protected]'           FROM DUAL;

Note: this includes an extra rows which does not contain ,,.

Outputs:

PARTY_ID PT_PARTNER_ID PT_PARTNER_EMAIL VALID_EMAIL_ID
3223218102E17 10101363 [email protected],,ALL [email protected]
5951118102E17 12059043 [email protected],, [email protected]
1113418102E17 12059044 [email protected],, [email protected]
2579321752017 36419244 [email protected],, [email protected]
ABC1234567890 12345678 [email protected] [email protected]

db<>fiddle here

Solution 3:[3]

Here is one option:

with test1 as(
                select '[email protected],,ALL' VALID_EMAIL_ID from dual union all 
                select '[email protected],,' VALID_EMAIL_ID from dual union all 
                select    '[email protected],,' VALID_EMAIL_ID from dual union all 
                select   '[email protected],,' VALID_EMAIL_ID from dual
              )
select upper(substr(VALID_EMAIL_ID,1,instr(VALID_EMAIL_ID,',,')-1)) from test1

Result:

[email protected]
[email protected]
[email protected]
[email protected]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 user3682728
Solution 2 MT0
Solution 3

Related Questions