SQL EMAIL PATCH

Asked

Viewed 74 times

0

Well I have several emails with problems at the end ex: hotmal without the . com @gmail without the . com, etc. What I am trying to do in oracle database, I look for gmail.com with the function regex_substr, if I don’t find it I want to use a regexp_replace to exchange the same, for several providers I use the regxp_substr to find and if he does not find trade. But I can’t use the function regexp_replace in the then, has anyone seen this? I searched and did not find.

follows the code

SELECT

email,

CASE  WHEN TRIM(UPPER(regexp_substr(EMAIL,'GMAIL.COM'))THEN 
REGEXP_REPLACE(UPPER(EMAIL),'GMAIL','GMAIL.COM')
END EMAIL

FROM EMAIL
sql oracle

  • Thought of a case-by-case Function ?

  • 1

    I thought , however I am afraid to use Creativity inside the Bank and bring down rsrs, I am an intern and already seen..

  • 1

    What error occurs ?

  • 1

    It was a logic error, where I didn’t pass a parameter. It follows the code for solution. &#xA;WHEN &#xA;REGEXP_INSTR(UPPER(TRIM(UPPER(REGEXP_REPLACE(regexp_substr(desc_email,'@[^.]*'),'@','')))),'COM')<1 AND REGEXP_INSTR(UPPER(desc_email),'GMAIL')>0 AND NOT REGEXP_INSTR(UPPER(desc_email),'GMAILCOM')>0 AND NOT REGEXP_INSTR(UPPER(desc_email),'GMAIL.COM')>0 THEN REGEXP_REPLACE(UPPER(DESC_EMAIL),'GMAIL','GMAIL.COM') Else email

  • 1

    @user162978, you answer your own question with the above solution.

  • 1

    @Can Gilson improve the title of your question? Reading it is not possible to find out what your question is

  • I changed, in case you have any tips I can improve

  • @Gilson can get it right by removing the uppercase letters: "Email correction in SQL", would look much better. But still its title could be more technical. Example: "I can’t use the regexp_replace function with then. How do I make a replacement with Oracle condition?"

Show 3 more comments

2 answers

2

Well ,I started to observe more the code was a logic error, where I did not pass a parameter. Follow the code for solution . 

WHEN REGEXP_INSTR(UPPER(TRIM(UPPER(REGEXP_REPLACE(regexp_substr(desc_email,'@[^.]*'),'@','')))), 'COM') < 1 
 AND REGEXP_INSTR(UPPER(desc_email),'GMAIL')>0 AND NOT REGEXP_INSTR(UPPER(desc_email),'GMAILCOM') > 0 
 AND NOT REGEXP_INSTR(UPPER(desc_email),'GMAIL.COM')>0 THEN REGEXP_REPLACE(UPPER(DESC_EMAIL),'GMAIL','GMAIL.COM') ELSE

And from that I replicate to other providers , however has much more detail to see.. examples, when I have email regexp_replace just for that..

2


I would suggest using other tools that SQL offers you.

Instead of using the regexp_instr and regexp_replace, try to use simpler functions such as substr,instr,like

I made an example that would be more practical to understand and solve this problem:

SELECT CASE
         --emails corretos
         WHEN INSTR(';GMAIL.COM;GMAIL.COM.BR;HOTMAIL.COM;HOTMAIL.COM.BR;',';' || SUBSTR(EMAIL,INSTR(EMAIL,'@') +1 ) || ';') > 0 THEN
           email
       --emails incorretos
       ELSE
         SUBSTR(email, 0, instr(email, '@')) || CASE WHEN email LIKE '%GMAIL%' THEN 'GMAIL.COM' 
                                                      WHEN email LIKE '%HOTMAIL%' THEN 'HOTMAIL.COM' 
                                                      WHEN email LIKE '%OUTLOOK%' THEN 'OUTLOOK.COM' END
       END email
  FROM (SELECT TRIM(UPPER(email)) email FROM email);

See rotating on SQL Fiddle.

This way just change the list of correct domains separated by ";" and adjust what should be done for each of the domains that present.

Makes it easier to maintain.

  • Really, it’s much simpler. Thank you, I broke my head a good while rsrs..

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.