hi,
RDBMS:Oracle 10gR2
I have a table with following structure
Table : t1
id int
emails varchar2 (200)
newEmail varchar2 (150)
Sample data is as following
id emails newEmail
1 email1@domain.com,email2@domain.com,email3@domain.com null
I can use following query to extract data from comma separation to row.
select id,regexp_substr(emails, '[^,]+', 1, rownum) Emails
from t1
connect by level <= length(regexp_replace(emails, '[^,]+')) + 1
;
Now I have to perform 2 operations
1- find /count email ids per id, to estimate how many ids has single and how much have multiple in emails.I have tried following but it just remain in execution
with Email as
(
select id,regexp_substr(emails, '[^,]+', 1, rownum) Emails
from t1
connect by level <= length(regexp_replace(emails, '[^,]+')) + 1
)
select id,emails from email
group by id,emails
having count (id)>1;
2- I want to write update newEmail field from first value in Emails in table t1 and remove updated value from emails field of table. so If emails has 5 email addresses (1,2,3,4,5) in it , after update it will have 4.I a not sure hoow to do that ?
thanks