1 2 3 4 Previous Next 47 Replies Latest reply: Jan 10, 2014 1:10 PM by Hoek Go to original post RSS
      • 15. Re: REGEXP_LIKE, oh where for art thou?
        Xev Bellringer

        Hi Greg,

         

        I think i understand what you said when you told me to put the "regexp's" in a table of sorts, and I will try that also, and see if I can incorporate that into the other program "Head Spins" (*)

         

         

        What a wonderful day this is:

         

        Thank you, Boneist, Greg, Frank and Hoek for helping me out today.

         

        • 16. Re: REGEXP_LIKE, oh where for art thou?
          Xev Bellringer

          Greg, it's Xev,

           

          I worked out all the patterns except the very last 2.

           

          I cannot seem to wrap my mind around these 2 formats.

           

          TID 777777777                                      3 alpha, space nine digits                                 

           

          K77-77-7777                                        1 alpha 2 dig - 2 dig - 4 dig

           

          I have the rest of them right here:

           

          with w_regexp as (

                   select '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}$'             regexp from dual union all  -- 6 numerics

                   select '^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$'  from dual union all  -- 8 numerics

                   select '^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$'  from dual union all  -- 9 numerics

                   select '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{7}$'             regexp from dual union all  -- 7 numerics

                   select '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{12}$'            regexp from dual union all  -- 12 numerics

                   select '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{11}$'            regexp from dual union all  -- 11 numerics

                   select '^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{13}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$'  from dual union all  -- 13 numerics

                   select '^([[:alpha:]]|[0-9])([0-9]{2}-){2}[0-9]{4}$'               from dual            -- hyphens

                )

          select *

          from fnd_patterns a,

               w_regexp b

          where regexp_like ( a.testcol, b.regexp )

          order by testcol desc

          /

          • 17. Re: REGEXP_LIKE, oh where for art thou?
            Xev Bellringer

            I just figured out one of them

             

            '^[[:alpha:]]{3}\s[0-9]{9}$' = for TID 777777777

             

            Now, I got to figure out the very last one...

             

             

            z77-77-7777                                        1 alpha 2 dig - 2 dig - 4 dig

            • 18. Re: REGEXP_LIKE, oh where for art thou?
              Greg Spall

              hint:

               

              there isn't much difference between:

               

              777-77-7777

              and

              z77-77-7777

               

               

               

              Both should be caught by this guy:

               

                select '^([[:alpha:]]|[0-9])([0-9]{2}-){2}[0-9]{4}$'               from dual            -- hyphens


              which breaks down to:


              ^                                -- beginning of line

              ([[:alpha:]]|[0-9])              -- 1 alpha OR 1 numeric.

              ([0-9]{2}-){2}                   -- ( 2 numerics AND hyphen ) ... twice

              [0-9]{4}                         -- 4 numerics

              $                                -- end of line.

              • 19. Re: REGEXP_LIKE, oh where for art thou?
                Xev Bellringer

                You know what Greg, I think in second place is a O, rather than a number....Geez...

                 

                It's format is XO7-77-7777, so 2 alpha, then 1 digit, dash 2 digits, then dash 4 digits...

                 

                 

                So how do I tell it that it's first 2 are alpha,followed by a digits right before the first dash?

                 

                Does this: ^([[:alpha:]]|[[:alpha:]] mean that it's starts with alpha twice??

                • 20. Re: REGEXP_LIKE, oh where for art thou?
                  Greg Spall

                  ok, so just adjust it for that ...

                   

                  so:

                   

                  777-77-7777

                  and

                  XO7-77-7777

                   

                  only those two?

                   

                  then just adjust this accordingly:

                   

                  ^                                -- beginning of line

                  ([[:alpha:]]{2}|[0-9]{2})        -- 2 alpha OR 2 numeric.

                  ([0-9]-)                         -- 1 numeric and hyphen

                  ([0-9]{2}-)                      -- ( 2 numerics AND hyphen )

                  [0-9]{4}                         -- 4 numerics

                  $                                -- end of line.


                  *Shrug*

                  That's the nice part about keeping the regexp's a little simpler .. easier to read/edit/maintain them if something subtle changes later

                   

                  (Just because you CAN do something, doesn't mean you SHOULD do that thing .. )

                  Reminds me of this:

                  http://blog.wolfram.com/2011/12/01/the-2011-mathematica-one-liner-competition/

                  Fun little exercise, but not always useful or practical in real life

                  • 21. Re: REGEXP_LIKE, oh where for art thou?
                    Xev Bellringer

                    Ohhh, this helped me tremdenously!!!! When you explained this to me just now

                    This break-down is just what I needed

                     

                     

                    ^                                -- beginning of line

                    ([[:alpha:]]{2}|[0-9]{2})        -- 2 alpha OR 2 numeric.

                    ([0-9]-)                         -- 1 numeric and hyphen

                    ([0-9]{2}-)                      -- ( 2 numerics AND hyphen )

                    [0-9]{4}                         -- 4 numerics

                    $                                -- end of line.


                    Big Hugs



                    • 22. Re: REGEXP_LIKE, oh where for art thou?
                      Greg Spall

                      No problem ..

                       

                      and as a standing "challenge" to those regexp "gurus" out there

                       

                      (Just because you CAN do something, doesn't mean you SHOULD do that thing .. )

                      I'd still love to see all that logic squashed into a single, minimal regexp - if possible lol Would be interesting never-the-less lol

                      • 23. Re: REGEXP_LIKE, oh where for art thou?
                        Xev Bellringer

                        It worked like a charm

                         

                         

                        select *

                        from fnd_pattern

                        where regexp_like(testcol, '^([[:alpha:]]{2}|[0-9]{2})([0-9]-)([0-9]{2}-)[0-9]{4}$')

                         

                         

                        • 24. Re: REGEXP_LIKE, oh where for art thou?
                          Xev Bellringer

                          Greg Wrote:

                           

                          No problem ..

                           

                          and as a standing "challenge" to those regexp "gurus" out there

                           

                          (Just because you CAN do something, doesn't mean you SHOULD do that thing .. )

                          I'd still love to see all that logic squashed into a single, minimal regexp - if possible lol Would be interesting never-the-less lol

                           

                           

                          Now, what I have to do with all of this is get it to work as right after I loop though a series of tables, then executes immediately, then does a select case using

                          our ever-loved [Regexp_Like]....I have no idea what this will do to the PGA .....But like you said, I should put all this regexp into a table, then loop through the table, then do the case select?

                          • 25. Re: REGEXP_LIKE, oh where for art thou?
                            Greg Spall

                            Xev Bellringer wrote:

                             

                            .But like you said, I should put all this regexp into a table, then loop through the table,

                            Woah .. slow down. I never said that

                             

                            What I said was:

                             

                            So based on that, I'm thinking it might help to put the regexp's into a "table" of sorts .. would make it easy to maintain and update if you have changes in future.


                            I mentioned no loop ..


                            Look at my original example:


                            with w_regexp as (

                                     select '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}$'            regexp from dual union all  -- 6 numerics

                                     select '^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$'  from dual union all  -- 8 numerics

                                     select '^([[:alpha:]]|[0-9])([0-9]{2}-){2}[0-9]{4}$'              from dual            -- hyphens

                                  )

                            select *

                            from fnd_patterns a,

                                 w_regexp b

                            where regexp_like ( a.testcol, b.regexp )

                            /


                            Now imagine you have a TABLE name "w_regexp" ...

                            with values such as:


                            REGEXP

                            ------------------------------------------

                            ^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}$

                            ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                            ....

                            etc.


                            Then they "query" becomes just:


                            select *

                            from fnd_patterns a,

                                 w_regexp b

                            where regexp_like ( a.testcol, b.regexp )

                            /


                            done ...

                            simple.  no loop.

                            just join ...


                            (and I promise nothing regards to performance on this O_o  depending on your data size, this might run very slow ... be careful ...)


                            • 26. Re: REGEXP_LIKE, oh where for art thou?
                              Xev Bellringer

                              Just for you Greg, This is all the RegExp i ended up with

                               

                               

                              ^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}$

                              ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                              ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                              ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                              ^[[:alpha:]]{3}\s[0-9]{9}$

                              ^([[:alpha:]]|[[:alpha:]]{3})[0-9]{12}$

                              ^([[:alpha:]]{2}|[0-9]{2})([0-9]-)([0-9]{2}-)[0-9]{4}$

                              ^([[:alpha:]]|[[:alpha:]]{3})[0-9]{11}$

                              ^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}$

                              ^([[:alpha:]]|[[:alpha:]]{3})[0-9]{7}$

                              ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{13}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                              ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                              ^([[:alpha:]]{2}|[0-9]{2})([0-9]-)([0-9]{2}-)[0-9]{4}$

                              ^([[:alpha:]]|[0-9])([0-9]{2}-){2}[0-9]{4}$

                              ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                              ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                              ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                              ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                              ^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$

                               

                              This will actually be able to pull back every single format that I need..

                               

                              Now, for a serious question, How in the world can I make this into one ' ' string so i can pass it into my select case statement?

                              • 27. Re: REGEXP_LIKE, oh where for art thou?
                                Frank Kulash

                                Hi,

                                 

                                 

                                Xev Bellringer wrote:

                                 

                                ... I'd still love to see all that logic squashed into a single, minimal regexp - if possible lol Would be interesting never-the-less lol

                                 

                                 

                                ...

                                 

                                There's a simple but uninteresting way to do it; just combine all the expressions with |.

                                REGEXP_LIKE ( testcol
                                            ,  '(^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6}$)|'                                                ||
                                               '(^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$)|'    ||
                                               '(^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$)|'    ||
                                               ...
                                            )

                                Since each pattern begins with ^ and ends with $, you can factor those out.

                                You might consider combining some of the similar patterns, like this:

                                REGEXP_LIKE ( testcol
                                            ,  '^([[:alpha:]]|[[:alpha:]]{3})[0-9]{6})|'                                                  ||
                                                '(([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{8,9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?)|'   ||
                                                ...
                                                '$'
                                            )

                                • 28. Re: REGEXP_LIKE, oh where for art thou?
                                  Xev Bellringer

                                  Opps, blush, I was meaning this other program I have that is doing the loop.

                                   

                                  This right here:

                                   

                                  set serveroutput on size unlimited

                                  execute DBMS_OUTPUT.ENABLE (buffer_size => NULL);

                                   

                                   

                                  ------------------------------------------------------------------------------------------------------------------------

                                  DECLARE

                                     look_fnd       INTEGER;

                                  ------------------------------------------------------------------------------------------------------------------------

                                     BEGIN

                                  FOR t  IN (SELECT owner, table_name, column_name

                                              FROM all_tab_columns

                                              WHERE owner = upper('&SCHEMA_NAME')

                                              AND table_name in (select table_name from all_tables

                                                                      where owner = 'ZZZ')) /*Modified to search all tables in ZZZ*/

                                                                               

                                  ------------------------------------------------------------------------------------------------------------------------                          

                                  LOOP

                                    BEGIN

                                      EXECUTE IMMEDIATE 'with a as ( select  case when REGEXP_LIKE(' || t.column_name ||

                                   

                                   

                                   

                                   

                                  /* This is where I pass in the regexp string */

                                   

                                   

                                  ',''^(19|20)\d\d[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])$'')

                                   

                                   

                                   

                                   

                                  then ''Match Found''

                                  else ''No Match Found'' end as output from ' || t.owner || '.' || t.table_name || ')

                                  select count(*) from a where a.output=''Match Found'' '

                                   

                                   

                                        INTO look_fnd ;

                                     

                                  IF look_fnd > 0         THEN

                                  DBMS_OUTPUT.put_line (

                                  t.owner

                                  || '.'

                                  || t.table_name

                                  || ' '

                                  || t.column_name

                                  || ' '

                                  || look_fnd);

                                    END IF;

                                    EXCEPTION

                                      WHEN OTHERS

                                        THEN

                                        DBMS_OUTPUT.put_line (

                                        'Genric Error '

                                        || t.column_name

                                        || ' from '

                                        || t.owner

                                        || '.'

                                        || t.table_name);    

                                        END;

                                     END LOOP;

                                  END;

                                  /

                                   

                                  Now, since i have to use this program, how can I get all those REGEXP's involved?

                                  • 29. Re: REGEXP_LIKE, oh where for art thou?
                                    Greg Spall

                                    Frank Kulash wrote:

                                     

                                    There's a simple but uninteresting way to do it; just combine all the expressions with |.

                                    Agreed, that was why I mentioned "minimal" - that's usually the real challenge with those things (and often interesting solutions).  I like seeing them, personally, I usually learn a trick or two from them (even if the answer as a whole isn't entirely practical in a prod solution )