1 2 Previous Next 15 Replies Latest reply: Mar 8, 2013 10:30 PM by jeneesh RSS

    Regex issue C999CCC pattern

    Dird
      Hi guys,

      I've been trying a couple things but my pattern matching doesnt work properly. Attempts are:
      sys> select username from dba_users where REGEXP_LIKE(username,'^[[:alpha:]][[:digit:]{3}][[:alpha:]{3}]$');
      no rows selected
      
      sys> select username from dba_users where REGEXP_LIKE(username,'([[:alpha:]])([[:digit:]{3}])([[:alpha:]{3}])');
      USERNAME
      ------------------------------
      IB03USER
      IB03IWAY
      IB03
      IB03BROKER
      U732NLE
      SD03
      T036AHE
      G637AHE
      What am I doing wrong that means it includes those other rows rather than only the 3 rows matching C999CCC? I tried searching threads but wasn't able to get the result trying different ways. Thanks for any info

      Mike
        • 1. Re: Regex issue C999CCC pattern
          Paul  Horth
          Dird wrote:
          Hi guys,

          I've been trying a couple things but my pattern matching doesnt work properly. Attempts are:
          sys> select username from dba_users where REGEXP_LIKE(username,'^[[:alpha:]][[:digit:]{3}][[:alpha:]{3}]$');
          no rows selected
          
          sys> select username from dba_users where REGEXP_LIKE(username,'([[:alpha:]])([[:digit:]{3}])([[:alpha:]{3}])');
          USERNAME
          ------------------------------
          IB03USER
          IB03IWAY
          IB03
          IB03BROKER
          U732NLE
          SD03
          T036AHE
          G637AHE
          What am I doing wrong that means it includes those other rows rather than only the 3 rows matching C999CCC? I tried searching threads but wasn't able to get the result trying different ways. Thanks for any info

          Mike
          you were nearly there
          with test_data as
          /* start test data */
          (
          select 'A123BBB' username from dual
          union all
          select 'C567DFG' from dual
          union all
          select 'AB23AAA' from dual
          union all
          select '92AAAAA' from dual
          union all
          select 'A456UUUU' from dual
          )
          /* end test data */
          select username from test_data where REGEXP_LIKE(username,'^[[:alpha:]][[:digit:]]{3}[[:alpha:]]{3}$');
          • 2. Re: Regex issue C999CCC pattern
            Manik
            Try:
            SELECT username
              FROM dba_users WHERE REGEXP_LIKE (username, '^[[:alpha:]]{1}[[:digit:]]{3}[[:alpha:]]{3}$');
            Cheers,
            Manik.
            • 3. Re: Regex issue C999CCC pattern
              chris227
              select
              *
              from 
              data
              where
              regexp_like (username, '^[[:alpha:]]\d{3}[[:alpha:]]{3}$')
              Edited by: chris227 on 08.03.2013 02:02
              Sorry mixed up \S with alpha

              Edited by: chris227 on 08.03.2013 02:22
              and as always corection leads to the next error, forgot the start-string sign. COFFEE ...
              • 4. Re: Regex issue C999CCC pattern
                dariyoosh
                Dird wrote:
                Hi guys,

                I've been trying a couple things but my pattern matching doesnt work properly. Attempts are:
                sys> select username from dba_users where REGEXP_LIKE(username,'^[[:alpha:]][[:digit:]{3}][[:alpha:]{3}]$');
                no rows selected
                
                sys> select username from dba_users where REGEXP_LIKE(username,'([[:alpha:]])([[:digit:]{3}])([[:alpha:]{3}])');
                USERNAME
                ------------------------------
                IB03USER
                IB03IWAY
                IB03
                IB03BROKER
                U732NLE
                SD03
                T036AHE
                G637AHE
                What am I doing wrong that means it includes those other rows rather than only the 3 rows matching C999CCC? I tried searching threads but wasn't able to get the result trying different ways. Thanks for any info

                Mike
                Try this
                WITH test_table AS
                (
                    SELECT 'IB03USER'   AS usrname FROM DUAL UNION
                    SELECT 'IB03IWAY'   AS usrname FROM DUAL UNION
                    SELECT 'IB03'       AS usrname FROM DUAL UNION
                    SELECT 'IB03BROKER' AS usrname FROM DUAL UNION
                    SELECT 'U732NLE'    AS usrname FROM DUAL UNION
                    SELECT 'SD03'       AS usrname FROM DUAL UNION
                    SELECT 'T036AHE'    AS usrname FROM DUAL UNION
                    SELECT 'G637AHE'    AS usrname FROM DUAL
                )
                SELECT usrname
                FROM test_table
                WHERE REGEXP_LIKE(usrname, '^(\w\d{3}\w{3})$');
                Regards,
                Dariyoosh
                • 5. Re: Regex issue C999CCC pattern
                  Dird
                  Damn I put the ] after the {3} >_< Thanks
                  • 6. Re: Regex issue C999CCC pattern
                    chris227
                    \w is not alpha
                    with data as (
                    select '1123BBB' username from dual
                    )
                    
                    SELECT username
                    FROM data
                    WHERE REGEXP_LIKE(username, '^(\w\d{3}\w{3})$');
                    
                    USERNAME 
                    1123BBB 
                    • 7. Re: Regex issue C999CCC pattern
                      Dird
                      Cool dariyoosh :) much more easy to write
                      • 8. Re: Regex issue C999CCC pattern
                        dariyoosh
                        Dird wrote:
                        Cool dariyoosh :) much more easy to write
                        Yes Chris227 is right, unfortunately I made a mistake \w is alphanumeric like [:alnum:] and not only letters. Therefore, you take Chris's solution which is the good one.

                        And thanks to Chris for this remark :)


                        Regards,
                        Dariyoosh
                        • 9. Re: Regex issue C999CCC pattern
                          Dird
                          Oh that sucks =( Seems strange that Oracle has so many of these regex values but didn't think to include a :alpha: only option

                          Mike
                          • 10. Re: Regex issue C999CCC pattern
                            dariyoosh
                            Dird wrote:
                            Oh that sucks =( Seems strange that Oracle has so many of these regex values but didn't think to include a :alpha: only option
                            Well, how about [a-zA-Z] ? :)

                            Regards,
                            Dariyoosh

                            Edited by: dariyoosh on Mar 8, 2013 11:41 AM
                            • 11. Re: Regex issue C999CCC pattern
                              jeneesh
                              dariyoosh wrote:
                              Well, how about [a-zA-Z] ? :)
                              Is this not same as [[:alpha:]] ?
                              • 12. Re: Regex issue C999CCC pattern
                                Dird
                                dariyoosh wrote:
                                Dird wrote:
                                Oh that sucks =( Seems strange that Oracle has so many of these regex values but didn't think to include a :alpha: only option
                                Well, how about [a-zA-Z] ? :)

                                Regards,
                                Dariyoosh

                                Edited by: dariyoosh on Mar 8, 2013 11:41 AM
                                Suppose but it's not as clean as something like \c

                                Mike
                                • 13. Re: Regex issue C999CCC pattern
                                  BluShadow
                                  jeneesh wrote:
                                  dariyoosh wrote:
                                  Well, how about [a-zA-Z] ? :)
                                  Is this not same as [[:alpha:]] ?
                                  Not necessarily the same thing.

                                  Depending on the character set being used, :alpha: can actually refer to more characters in the character set than just a-z and A-Z.

                                  Run this, to check against your own character set...
                                  select level-1 as asc_code, decode(chr(level-1), regexp_substr(chr(level-1), '[[:print:]]'), CHR(level-1)) as chr,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:graph:]]'), 1) is_graph,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:blank:]]'), 1) is_blank,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:alnum:]]'), 1) is_alnum,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:alpha:]]'), 1) is_alpha,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:digit:]]'), 1) is_digit,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:cntrl:]]'), 1) is_cntrl,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:lower:]]'), 1) is_lower,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:upper:]]'), 1) is_upper,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:print:]]'), 1) is_print,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:punct:]]'), 1) is_punct,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:space:]]'), 1) is_space,
                                         decode(chr(level-1), regexp_substr(chr(level-1), '[[:xdigit:]]'), 1) is_xdigit
                                    from dual
                                  connect by level <= 256
                                  /
                                  and you'll likely see other characters in the is_alpha set...
                                  • 14. Re: Regex issue C999CCC pattern
                                    chris227
                                    jeneesh wrote:
                                    dariyoosh wrote:
                                    Well, how about [a-zA-Z] ? :)
                                    Is this not same as [[:alpha:]] ?
                                    No.It all depends on NLS_CHARACTERSET and NLS_SORT.
                                    With A-Z a explicit range is defined based on the alphabet of the underlying language, with [:alpha:] a set of a l l characters fo the alphabet of the underlying language is defined.
                                    There may be characters outside the a defined range.
                                    SELECT 
                                    *
                                    FROM V$NLS_PARAMETERS
                                    where
                                    parameter in ('NLS_CHARACTERSET', 'NLS_SORT')
                                    
                                    PARAMETER VALUE 
                                    NLS_CHARACTERSET AL32UTF8 
                                    NLS_SORT BINARY 
                                    
                                    
                                    SELECT 
                                     REGEXP_INSTR ('نام', '[[:alpha:]]', 1,1) a
                                    ,REGEXP_INSTR ('نام', '[A-Za-z]', 1,1) b
                                    FROM DUAL
                                    
                                    A B 
                                    1 0 
                                    
                                    another example
                                    
                                    NLS_SORT='GERMAN'
                                    
                                    With data as (
                                    select 'ä' s from dual
                                    union all
                                    select 'a' s from dual
                                    )
                                    
                                    Select
                                     regexp_substr(s, '[A-Za-z]') R
                                    ,regexp_substr(s, '[[:alpha:]]') S
                                    from
                                    data
                                    
                                    R     S
                                    "ä"     "ä"
                                    "a"     "a"
                                    
                                    alter session set NLS_SORT='BINARY';
                                    
                                    With data as (
                                    select 'ä' s from dual
                                    union all
                                    select 'a' s from dual
                                    )
                                    
                                    Select
                                     regexp_substr(s, '[A-Za-z]') R
                                    ,regexp_substr(s, '[[:alpha:]]') S
                                    from
                                    data
                                    
                                    R     S
                                         "ä"
                                    "a"     "a"
                                    here is a link covering this issue:
                                    http://www.oracle.com/technetwork/database/focus-areas/application-development/twp-regular-expressions-133133.pdf

                                    Edited by: chris227 on 08.03.2013 03:45
                                    alter session set NLS_SORT='BINARY'; added
                                    1 2 Previous Next