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],,

CodePudding user response：

please suggest how can I achive this.

try using these

0. Upper - Make all to upper case https://www.techonthenet.com/oracle/functions/upper.php
1. Instr - find the position of ,, https://www.techonthenet.com/oracle/functions/instr.php
2. 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

CodePudding user response：

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

CodePudding user response：

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]