This discussion is archived
3 Replies Latest reply: Aug 14, 2009 8:45 AM by Hoek RSS

Find non alpha characters in a string/email

user527060 Newbie
Currently Being Moderated
HI,
I'm on 10g database.

I want to find out a list of email address where there are non alpha characters in an email .

Example
KEY1 EMAIL
1       ABCD02-#0239@AOL.NET
2       ABCD@AOL.NET
3       AXIAP#-0223.@AOL.NET
How can i find out the emails with "#" and "-" characters only.

Thanks

Edited by: user527060 on Aug 14, 2009 8:15 AM
  • 1. Re: Find non alpha characters in a string/email
    Centinul Guru
    Currently Being Moderated
    This may work:
    SQL> WITH test_data AS
      2  (
      3          SELECT 1 AS KEY1, 'ABCD02-#0239@AOL.NET' AS EMAIL FROM DUAL UNION ALL
      4          SELECT 2 AS KEY1, 'ABCD@AOL.NET' AS EMAIL FROM DUAL UNION ALL
      5          SELECT 2 AS KEY1, 'AXIAP#-0223.@AOL.NET' AS EMAIL FROM DUAL
      6  )
      7  SELECT  *
      8  FROM    test_data
      9  WHERE   REGEXP_LIKE
     10          (
     11                  EMAIL
     12          ,       '[#|-]'
     13          )
     14  /
    
          KEY1 EMAIL
    ---------- --------------------
             1 ABCD02-#0239@AOL.NET
             2 AXIAP#-0223.@AOL.NET
  • 2. Re: Find non alpha characters in a string/email
    Hoek Guru
    Currently Being Moderated
    Hi,

    Try
    SQL> with t as ( -- Generating sample data here:
      2  select 1 key1, 'ABCD02-#0239@AOL.NET' email from dual union all
      3  select 2,'ABCD@AOL.NET' from dual union all
      4  select 3,'AXIAP#-0223.@AOL.NET' from dual
      5  ) -- Actual query starts here
      6  select key1
      7  ,      email
      8  from   t
      9  where regexp_like(substr(email, 1, instr(email, '@') -1), '[[:punct:]]');
    
          KEY1 EMAIL
    ---------- --------------------
             1 ABCD02-#0239@AOL.NET
             3 AXIAP#-0223.@AOL.NET
    
    2 rows selected.
  • 3. Re: Find non alpha characters in a string/email
    Hoek Guru
    Currently Being Moderated
    Hey Centinul, you have a DUP_VAL_ON_INDEX ;)
    It's friday after all

    SQL> with t as (
      2  select 1 key1, 'ABCD02-#0239@AOL.NET' email from dual union all
      3  select 2,'ABCD@AOL.NET' from dual union all
      4  select 3,'AXIAP#-0223.@AOL.NET' from dual
      5  )
      6  select key1
      7  ,      email
      8  from   t
      9  where  length(regexp_replace(substr(email, 1, instr(email, '@') -1), '[[:alnum:]]')) is not null;
    
          KEY1 EMAIL
    ---------- --------------------
             1 ABCD02-#0239@AOL.NET
             3 AXIAP#-0223.@AOL.NET
    
    2 rows selected.
    http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html
    http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/rischert_regexp_pt2.html

    However regexp consume CPU, why not just simply:
    SQL> with t as (
      2  select 1 key1, 'ABCD02-#0239@AOL.NET' email from dual union all
      3  select 2,'ABCD@AOL.NET' from dual union all
      4  select 3,'AXIAP#-0223.@AOL.NET' from dual
      5  )
      6  select key1
      7  ,      email
      8  from   t
      9  where  email like '%#%'
     10  or     email like '%-%';
    
          KEY1 EMAIL
    ---------- --------------------
             1 ABCD02-#0239@AOL.NET
             3 AXIAP#-0223.@AOL.NET
    
    2 rows selected.
    if you're only interested in finding # or - occurences in an emailaddress?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points