1 2 3 4 Previous Next 47 Replies Latest reply: Jan 10, 2014 1:10 PM by Hoek RSS

    REGEXP_LIKE, oh where for art thou?

    Xev Bellringer

      Hi Guys, it's Xev.

       

      I am using regexp_like and I have a search string that will find nine consecutive digits and nine digits with dashes in them.

      Here is that string: '[0-9]{9}$|^[0-9]{3}-[0-9]{2}-[0-9]{4}$'

       

      Now, I need to add to this string to tell it to find Alpha Numeric also....

       

      I have seen regexp examples like this:

       

      SELECT *

      FROM test

      WHERE REGEXP_LIKE(testcol, '[[:alnum:]]');

       

       

      SELECT *

      FROM test

      WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{3}');

       

       

      SELECT *

      FROM test

      WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{5}');

       

      These examples above bring back things like "letters" (a-z) that has no numbers in them, they make the search to broad.

       

      My question is how do I add the [[:alnum:]] to the  '[0-9]{9}$|^[0-9]{3}-[0-9]{2}-[0-9]{4}$' and it find different combinations of 12,13,9,8 and 6 Alpha Numeric numbers.

       

      Thanks

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

          Hi,

          Xev Bellringer wrote:

           

          Hi Guys, it's Xev.

           

          I am using regexp_like and I have a search string that will find nine consecutive digits and nine digits with dashes in them.

          Here is that string: '[0-9]{9}$|^[0-9]{3}-[0-9]{2}-[0-9]{4}$'

           

          Now, I need to add to this string to tell it to find Alpha Numeric also....

           

          I have seen regexp examples like this:

           

          SELECT *

          FROM test

          WHERE REGEXP_LIKE(testcol, '[[:alnum:]]');

           

           

          SELECT *

          FROM test

          WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{3}');

           

           

          SELECT *

          FROM test

          WHERE REGEXP_LIKE(testcol, '[[:alnum:]]{5}');

           

          These examples above bring back things like "letters" (a-z) that has no numbers in them, they make the search to broad.

           

          My question is how do I add the [[:alnum:]] to the  '[0-9]{9}$|^[0-9]{3}-[0-9]{2}-[0-9]{4}$' and it find different combinations of 12,13,9,8 and 6 Alpha Numeric numbers.

           

          Thanks

          It depends on what you want.  Your requirements may be clear to you, but to me they are a riddle, wrapped in a mystery, inside an enigma.

           

          Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
          Explain, using specific examples, how you get those results from that data.  In this case, point out why each row of the sample data is or is not included in the results.
          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002

           

          Maybe you just need to use   [[:alnum:]]  in the places where you're now using   [0-9]   .

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

            Hi Frank, sorry about that, here is some sample data, these are the alpha numeric formats that I am having to deal with. (I am on 11g Version 11.2.0.1.0)

            I have this search string, and it helps me find nine consecutive digits and then nine digits with dashes, but that's all I have figured out.

             

            SELECT *

            FROM fnd_test

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

             

             

            There is 18 different combinations that I need to search on.

             

            TESTCOL                                            COMMENTS                                               

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

            WVE777777                                          3 alpha, 6 consecutive                                   

            TXDL 77777777                                      4 alpha prefix space, 8 digits                           

            TX77777777                                           2 alpha 8 digits consecutive                             

            TX 77777777                                          2 chars in front, one space and then eight digits.       

            TID 777777777                                       3 alpha, space nine digits                               

            S777777777777                                     1 alpha and then  12 digits                            

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

            G77777777777                                       1 alpha 11 digits                                        

            E777777                                                1 alpha, 6 consecutive                                   

            D7777777                                              1 alpha 7 consecutive                                  

            7777777777777                                      thirteen digits consecutive.                             

            777777777                                             nine digit consecutive.                                  

            777-77-7777                                           nine with dashes.                    

            77777777                                              eight consecutive digits                                 

            77777777 TX                                         eight digit with 2 Alpha suffix

            77777777 TXID                                      eight digits 1 space 4 alpha suffix                      

            777777777 LA                                       nine digits and then 1 space then 2 alpha suffix.  

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

              here is some sample data,

               

              No, that's not sample data, Frank specifically asked you to "Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data."

              As an alternative for CREATE TABLE and INSERT INTO statements, youy can use the WITH clause....

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

                Hi,

                 

                Once again, you need to post CREATE TABLE and INSERT statements for the sample data, the results you want from that sample data, and a row-by-row explanation of why you want those results from that data.

                Include some examples where the given string does not match any of the target patterns.

                 

                No kidding, you need to read (and follow) the forum FAQ: https://forums.oracle.com/message/9362002

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

                  Ok, sorry guys, is this ok?

                   

                   

                   

                  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 ('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 ('TX 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 with format xxx-xx-xxxx');

                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('77777777 TX','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 ('TID 777777777','3 alpha, space nine digits');

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

                  Insert into SMEG.FND_PATTERNS (TESTCOL,COMMENTS) values ('K77-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 TXXX','eight digits 1 space 4 alpha suffix');

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

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

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

                   

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

                   

                   

                   

                   

                   

                  Here is the only SQL I have that will bring back nine digits consecutive numbers and then also nine digits with the dashes wit the format of xxx-xx-xxxx

                   

                   

                   

                  SELECT *

                  FROM FND_PATTERNS

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

                   

                   

                  TESTCOL                                            COMMENTS                                               

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

                  777-77-7777                                        nine with dashes. FORMAT xxx-xx-xxxx              

                  777777777                                          nine digit consecutive.         

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

                    What's the difference between these 2??

                     

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

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

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

                      I just noticed that, it's a duplicate. I just re-edited the post to take that out.

                       

                      Do you know anything about regexp_like? I think i am getting close to figuring it out, but I have to test it to make sure I don't pull back things I don't want...

                      • 8. Re: REGEXP_LIKE, oh where for art thou?
                        Boneist

                        You're probably after the [:alpha:] class, eg:

                         

                        with sample_data as (select 'TXDL 16939441' testcol, '4 alpha, space, 8 digits.' comments from dual union all

                                             select 'WVE892102' testcol, '3 alpha, 6 digits.' comments from dual union all

                                             select 'TID 445689166' testcol, '3 alpha, space, nine digits' comments from dual union all

                                             select 'TX21334376' testcol, '2 alpha, 8 digits.' comments from dual union all

                                             select 'TX 12786049' testcol, '2 alpha, space, 8 digits.' comments from dual union all

                                             select 'S362420598030' testcol, '1 alpha, 12 digits.' comments from dual union all

                                             select 'G20063161647' testcol, '1 alpha, 11 digits.' comments from dual union all

                                             select 'D2208703' testcol, '1 alpha, 7 digits.' comments from dual union all

                                             select 'E102795' testcol, '1 alpha, 6 digits.' comments from dual union all

                                             select 'K01-44-4855' testcol, '1 alpha, 2 digits, dash, 2 digits, dash, 4 digits.' comments from dual union all

                                             select '5563107788760' testcol, '13 digits consecutive.' comments from dual union all

                                             select '466767189' testcol, '9 digits.' comments from dual union all

                                             select '23386895' testcol, '8 digits.' comments from dual union all

                                             select '777-77-7777' testcol, '3 digits, dash, 2 digits, dash, 4 digits.' comments from dual union all

                                             select '16618667TX' testcol, '8 digits, 2 alpha.' comments from dual union all

                                             select '14067641 TX' testcol, '8 digits, space, 2 alpha.' comments from dual union all

                                             select '003671363 LA' testcol, '9 digits, space, 2 alpha.' comments from dual union all

                                             select '11946262 TXID' testcol, '8 digits, space, 4 alpha.' comments from dual union all

                                             select '1111111111' testcol, 'shouldn''t match - 10 consecutive digits' from dual)

                        select testcol,

                               comments,

                               case when regexp_like(testcol,'^[[:alpha:]]{4}\s[0-9]{8}$'||

                                                             '|^[0-9]{9}$'||

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

                                         then 'Y' else 'N' end check_col

                        from   sample_data;

                         

                        (I've left the rest as an exercise for the OP; also I took the liberty of making a difference between the two sets of (effectively) 8 digits with 2 alpha, by making one have a space and the other not. It wasn't difficult to come up with the rest of the conditions...)

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

                          WOW! Thanks Boneist,

                          I will test that out right now and hugs!

                           

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

                            Hi,

                             

                            The sample data is okay.  As Greg said, testcol is not unique; is that what you want?

                             

                            Now what are the results you want from that sample data?  Explain why you want those results.  When rows are not included in the results, explain why not.

                            • 11. Re: REGEXP_LIKE, oh where for art thou?
                              Boneist

                              I should point out that I put the regexp_like in the select clause to make testing easier, whilst I built up the matching patterns. Obviously, if you're going to be using it to filter the rows, you'd use it directly in the where clause!

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

                                Hi Frank,

                                 

                                I think Boneist did what I need, I am testing what Boneist did, I'll post the results on that ASAP.

                                 

                                What I am ultimately doing with is using it as a part of another program that goes in and scours tables for string inside of fields. This piece must only bring back those formats

                                of numbers, so I can have solid results.

                                 

                                The rows that are not included in the results need to be just let out all together. I just need to be able to get this to work with the other code that is doing the actual loops and searches.

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

                                  Boneist wrote:


                                  I should point out that I put the regexp_like in the select clause to make testing easier, whilst I built up the matching patterns. Obviously, if you're going to be using it to filter the rows, you'd use it directly in the where clause!


                                  Xev Wrote,

                                  Yes, thank you once again Boneist, you made my day! I think this is exactly what I need. This piece will actually be apart of another program that does the loops ans sql conditions, but I will use the clause directly!!

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

                                    I'm looking at the logic, thinking you have some pretty "wide" requirements ... I'd probably consider breaking down the problem. In other words, do NOT create regexp that matches all 18 of those cases at once.  You need to find patterns.

                                     

                                    I noticed the following ... if I rearrange the data like so .. you can see some much easier patterns:

                                     

                                     

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

                                    Insert into FND_PATTERNS (TESTCOL,COMMENTS) values ('WVE777777','3 alpha, 6 consecutive ');

                                     

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

                                     

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

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

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

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

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

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

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

                                     

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

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

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

                                     

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

                                     

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

                                     

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

                                     

                                    Insert into FND_PATTERNS (TESTCOL,COMMENTS) values ('777-77-7777','nine with dashes with format xxx-xx-xxxx');

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

                                     

                                    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 use a WITH clause to mimic that ... but whatever)

                                     

                                    Once it's rearranged like that, creating the regexp for each smaller "group" becomes almost trivial ...

                                     

                                    Not sure if there's a better way, but this seems to work .. *shrug*

                                     

                                    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 )

                                    /

                                     

                                    Again, just a couple shown, should be easy to build the others.

                                     

                                    1 2 3 4 Previous Next