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

        Xev Bellringer wrote:

         

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

         

        Why?

        Why do you "have to use this program" ?

         

        I don't see a need to do it that way, simple join (or outer/inner join? if you need to show non-matches) gives you your answer. *shrug*

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

          Greg, this has to search all the tables per owner for all these patterns.

           

          If I just concatenate all the regexp logic with a ||, it should work right?

           

          Or should i try another approach? I need counts for all the findings. It worked so far for me, but this is the first for so much regexp logic.

           

          Before i was just passing in two different strings...

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

            Perhaps others have other suggestions, but I would do the following:

             

            a) create a table that contains 1 row for each of the regexp strings we came up with.

            b) loop through each table and build a query like this:

            select a.<column_name>, 'Match Found'  found

              from <table_name> a, w_regexp b

            where regexp_like ( a.<column_name>, b.regexp )

             

              (and just pull back the matches - logic is anything else is not a match )

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

              Hi Frank, Good to see you again.

               

              Honestly, I am going to try this first, since I am kinda pressed for time with this...

               

              I just hope it won't bring the database to it's knees.

               

              Is using regexp this way a "performance killer?"

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

                Greg Wrote:

                 

                Perhaps others have other suggestions, but I would do the following:

                 

                a) create a table that contains 1 row for each of the regexp strings we came up with.

                b) loop through each table and build a query like this:

                select a.<column_name>, 'Match Found'  found

                  from <table_name> a, w_regexp b

                where regexp_like ( a.<column_name>, b.regexp )

                 

                  (and just pull back the matches - logic is anything else is not a match )

                 

                 

                Ok, If i can think of a way to do it this way, I will

                 

                So basically, I can use the existing logic but have it build the regexp from a table, would this be better for Database Performance also?

                I am doing this to 624 Tables, and some of the table have 139 columns, and a row count of 40 Plus Million Rows.....

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

                  Greg Wrote:

                   

                  Ok, I just saw this line

                   

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


                  I am running it a (Quality Assurance Instance), so what I will do is wait till everyone is gone before i run this


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

                    Franks, I am having a hard time concatenating all this Regexp logic we came up with the other day. The syntax is super tedious.

                     

                    Here is all the regexp logic..

                     

                     

                    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})?$' regexp from dual union all  -- 8 numerics

                             select '^([[:alpha:]]{2}|[[:alpha:]]{4})?[ ]?[0-9]{9}[ ]?([[:alpha:]]{2}|[[:alpha:]]{4})?$' regexp  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:]]{3}\s[0-9]{9}$'                                         regexp from dual union all  -- 3 alpha nine digs.

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

                             select '^([[:alpha:]]{2}|[0-9]{2})([0-9]-)([0-9]{2}-)[0-9]{4}$'             regexp from dual union all  -- PP1-77-7777 finds format.

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

                                  

                          )

                    select *

                    from fnd_govids a,

                         w_regexp b

                    where regexp_like ( a.testcol, b.regexp )

                    order by testcol desc

                    /

                     

                    This gets all the strings I need, but I am now passing in all this logic to another program i mentioned in the post.

                    The way the program works is like this:

                    I can pass in one string and it works just fine. This is the syntax I use for one string.

                     

                    ',''^[0-9]{9}$|^[0-9]{3}-[0-9]{2}-[0-9]{4}$'')

                     

                    Using this syntax, it works fine with one, but How can I concatenate all these above??

                     

                     

                    Here is the entire program:

                     

                     

                    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 data_type IN ('VARCHAR2','TIMESTAMP(6) WITH TIME ZONE','TIMESTAMP(6)','NUMBER','CHAR','DATE')

                                AND owner = upper('&SCHEMA_NAME')

                                and table_name = upper('&TABLE_NAME')) /*For Testing Purposes*/

                                /*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 ||

                       

                       

                       

                    /*The search string below looks for nine digit consecutive digits and also nine digit with the dashes.*/   

                    ',''^[0-9]{9}$|^[0-9]{3}-[0-9]{2}-[0-9]{4}$'')

                     

                     

                     

                     

                     

                     

                     

                     

                    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;

                    /

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

                      Hi,

                       

                       

                      Xev Bellringer wrote:

                       

                      ... I just hope it won't bring the database to it's knees.

                       

                      Is using regexp this way a "performance killer?"

                      You pay a price for using any function or operator.  The price for the regular expression functions is significantly higher than the price for less powerful functions, such as SUBSTR, so it's often more efficient to do things using less powerful functions, even if you have to call 3 or 4 simple functions instead of 1 regular expression function.  I don't believe this problem is one of those cases.  While you could certainly do what you need to without regular expressions, I think it would involve so many other functions that it would be slower.  You could also do what you want in a user-defined PL/SQL function, but the cost of swithing back and forth between SQL and PL/SQL is high.
                      I think regular expressions are your best bet.  If you have time later, you might experiment with simpler functions, or PL/SQL, but I suggest getting a regular expression solution working first.

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

                        Hi,

                         

                         

                        Xev Bellringer wrote:

                         

                        ...
                        Using this syntax, it works fine with one, but How can I concatenate all these above??
                        ...

                         

                         

                        Didn't I show how to do that yesterday?  See reply #27, above

                         

                        If you're having trouble using that technique, post the code where you tried.  Get it working for 2 or 3 patterns first.  Once you know how to concatenate 2 or 3 patterns, adding any number of additional patterns will be easy.

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

                          Frank Wrote:

                           

                          You pay a price for using any function or operator.  The price for the regular expression functions is significantly higher than the price for less powerful functions, such as SUBSTR, so it's often more efficient to do things using less powerful functions, even if you have to call 3 or 4 simple functions instead of 1 regular expression function.  I don't believe this problem is one of those cases.  While you could certainly do what you need to without regular expressions, I think it would involve so many other functions that it would be slower.  You could also do what you want in a user-defined PL/SQL function, but the cost of swithing back and forth between SQL and PL/SQL is high.

                          I think regular expressions are your best bet.  If you have time later, you might experiment with simpler functions, or PL/SQL, but I suggest getting a regular expression solution working first.


                          I really like this explanation Frank. This is very good for me to know. I have used regexp before and i watched it hit the larger tables harder, and noticed that the largest punch in the whole script running process was when it was using the regexp, but as you mention above, even if i used less powerful functions, it might actually be slower.

                          I thought about using something like Bulk Collect and the limit clause, but it would just seem like it would not be very effective since I am having to scan per row, per column, for these patterns.

                          Some of these tables have 139 columns in just one table. The largest table has 40 Million plus rows, and like you say, switching back and fourth between SQL and PL/SQL is high.


                          thanks Frank.


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

                            Frank Wrote:

                             

                            Didn't I show howto do that yesterday?  See reply #27, above

                             

                            If you're having trouble using that technique, post the code where you tried.  Get it working for 2 or 3 patterns first.  Once you know how to concatenate 2 or 3 patterns, adding any number of additional patterns will be easy.

                             

                            Ok, I will try to get it to work with just 2 strings first.

                             

                            I have this in the program and  it's giving me a syntax error.

                             

                             

                             

                              ',' '(^([[: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})?$)|'    ||

                                          

                                        )

                             

                            It doesn't seem to like this syntax.

                            • 41. Re: REGEXP_LIKE, oh where for art thou?
                              Hoek

                              On a side note:

                              Please remove this bug from your code and never ever use it again:

                               

                              EXCEPTION

                                  WHEN OTHERS

                                    THEN

                                    DBMS_OUTPUT.put_line (

                                    'Genric Error '

                                    || t.column_name

                                    || ' from '

                                    || t.owner

                                    || '.'

                                    || t.table_name);      

                               

                              You're swallowing exceptions, which is one of the worst practices a coder can do.

                              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1509245700346768268#tom1535781400346575552

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

                                Hi,

                                 

                                 

                                Xev Bellringer wrote:

                                 

                                Frank Wrote:

                                 

                                Didn't I show howto do that yesterday?  See reply #27, above

                                 

                                If you're having trouble using that technique, post the code where you tried.  Get it working for 2 or 3 patterns first.  Once you know how to concatenate 2 or 3 patterns, adding any number of additional patterns will be easy.

                                 

                                Ok, I will try to get it to work with just 2 strings first.

                                 

                                I have this in the program and  it's giving me a syntax error.

                                 

                                 

                                 

                                  ',' '(^([[: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})?$)|'    ||

                                            

                                            )

                                 

                                It doesn't seem to like this syntax.

                                 

                                Post your complete query, so the people who want to help you can run it themselves to reproduce the error and test their ideas.

                                I can see lots of mistakes in the code above:

                                • No SELECT keyword
                                • No function
                                • ) without matching (
                                • ...

                                Depending on what you didn't post, hsoe things might not be problems at all.

                                 

                                 

                                You posted 3 strings, not 2; but that's okay.

                                When concatentating N strings, use the || operator N-1 times.  Do NOT put a || operator after the last string.

                                Depending on your front end, a completely blank line in the middle of the statement might be a problem.  For now, don't use completely balnk lines in pure SQL code.  If you really want to, start another thread after you get this problem solved.

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

                                  Frank Wrote:

                                   

                                  Post your complete query, so the people who want to help you can run it themselves to reproduce the error and test their ideas.

                                   

                                  You posted 3 strings, not 2; but that's okay.

                                  When concatentating N strings, use the || operator N-1 times.  Do NOT put a || operator after the last string.

                                   

                                  Ok, here is the test data table create scripts, so they can create the test data and then run the script to see what I am talking about.

                                  The will have to modify the script just a tad in order for it to work in their database of course, but that is minor obvious stuff, but I will make a comment in the script.

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

                                  CREATE TABLE "SMEG"."FND_PATTERNS"

                                     ( "TESTCOL" VARCHAR2(50 BYTE),

                                    "COMMENTS" VARCHAR2(60 BYTE)

                                     ) SEGMENT CREATION IMMEDIATE

                                    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

                                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

                                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

                                    TABLESPACE "SMEG" ;

                                  REM INSERTING into SMEG.FND_PATTERNS

                                  SET DEFINE OFF;

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('abcde',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('12345',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('1a4A5',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('12a45',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('12aBC',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('12abc',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('12ab5',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('12aa5',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('12AB5',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('ABCDE',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('123-5',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('12.45',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('1a4b5',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('1 3 5',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('1  45',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('1   5',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('a  b  c  d',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('a b  c   d    e',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('a              e',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('Steven',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('Stephen',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('111.222.3333',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('222.333.4444',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('333.444.5555',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('abcdefabcdefabcxyz',null);

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('77777777 TX','eight digits and then 2 alpha suffix');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('77777777','eight consecutive digits');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('ZZ 77777777','2 chars in front, one space and then eight digits.');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('777-77-7777','nine with dashes, looks like an ssn!');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('77777777 ZZ','eight digit with Alpha TX on end.');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('777777777','nine digit consecutive.');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('S777777777777','one alpha and then  12 digits');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('7777777777777','thirteen digits consecutive.');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('D7777777','one alpha 7 consecutive');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('ZZZ 777777777','3 alpha, space nine digits');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('777777777 ZZ','nine digits and then 1 space then 2 alpha suffix.');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('ZZ7-77-7777','1 alpha 2 dig - 2 dig - 4 dig');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('G77777777777','1 alpha 11 digits');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('E777777','1 alpha, 6 consecutive ');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('77777777 ZZZZ','eight digits 1 space 4 alpha suffix');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('ZZZZ 77777777','4 alpha prefix space, 8 digits');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('ZZ77777777','2 alpha 8 digits consecutive');

                                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('ZZZ777777','3 alpha, 6 consecutive ');

                                   

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

                                  Now, here is the actual script:

                                   

                                  set serveroutput on size unlimited

                                  execute DBMS_OUTPUT.ENABLE (buffer_size => NULL);

                                   

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

                                  DECLARE

                                     FND_LOOK       INTEGER;

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

                                     BEGIN

                                  FOR t  IN (SELECT owner, table_name, column_name

                                              FROM all_tab_columns

                                              WHERE data_type IN ('VARCHAR2','TIMESTAMP(6) WITH TIME ZONE','TIMESTAMP(6)','NUMBER','CHAR','DATE')

                                              AND owner = upper('&SCHEMA_NAME')

                                              and table_name = upper('&TABLE_NAME')) /*For Testing Purposes*/

                                              /*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 ||

                                     

                                     

                                     

                                  /*The search string below looks for nine digit consecutive digits and also nine digit with the dashes, BUT I NEED TO ALL ALL THE REGEXP LOGIC LISTED ON THE VERY BOTTOM OF THE POST*/

                                   

                                   

                                  ','     '^[0-9]{9}$|^[0-9]{3}-[0-9]{2}-[0-9]{4}$'')

                                   

                                   

                                   

                                                  

                                               

                                   

                                   

                                   

                                   

                                   

                                   

                                  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 FND_LOOK ;

                                       

                                  IF FND_LOOK > 0         THEN

                                  DBMS_OUTPUT.put_line (

                                  t.owner

                                  || '.'

                                  || t.table_name

                                  || ' '

                                  || t.column_name

                                  || ' '

                                  || FND_LOOK);

                                    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 here is all the RegExp logic that needs to be passed in a concatenated strings...

                                   

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

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

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

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

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

                                  '^[[:alpha:]]{3}\s[0-9]{9}$' -- 3 alpha nine digs.

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

                                  '^([[:alpha:]]{2}|[0-9]{2})([0-9]-)([0-9]{2}-)[0-9]{4}$ -- PP1-77-7777 finds format.

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



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

                                    Hoek Wrote:

                                     

                                    On a side note:

                                    Please remove this bug from your code and never ever use it again:

                                     

                                    EXCEPTION

                                        WHEN OTHERS

                                          THEN

                                          DBMS_OUTPUT.put_line (

                                          'Genric Error '

                                          || t.column_name

                                          || ' from '

                                          || t.owner

                                          || '.'

                                          || t.table_name);     

                                     

                                    You're swallowing exceptions, which is one of the worst practices a coder can do.

                                    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1509245700346768268#tom1535781400346575552

                                     

                                    Hi Hoek,

                                    I wasn't sure what to do other than this. It was like a catch all until i could get the actual logic working.

                                     

                                    Can you please provide me an alternative way to deal with the logic at this point in the program?  I am open to any and all suggestions