3 Replies Latest reply: Aug 14, 2009 10:45 AM by Hoek RSS

    Find non alpha characters in a string/email

    user527060
      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
          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
            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
              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?