Home » SQL & PL/SQL » SQL & PL/SQL » regular expression with owa pattern (oracle 11)
regular expression with owa pattern [message #402070] Thu, 07 May 2009 08:34 Go to next message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Hello,
could anyone help me with this regexp problem, please?
thanks in advance.

I have these items on input:
112233
1223568
112233 hey man...
112233xxxyyy

And I want filter out only those, using owa pattern - "match" function, which conform to either:
a) exactly six ciphers
b) six ciphers, whitespace, and then anything

I tried something like:
^\d{6}[\s\$]
Sometimes this is 0K, but in Oracle understanding, it is syntax error. (Oracle has problem with \s and $ in square brackets)

I want to say: "Six ciphers, and then either end of string or whitespace".
So, in my example above, only line 1 and 3 is OK.

Any idea?
Thanks very much for any hint.
Jan
Re: regular expression with owa pattern [message #402076 is a reply to message #402070] Thu, 07 May 2009 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2    select '112233' val from dual
  3    union all
  4    select '1223568' from dual
  5    union all
  6    select '112233 hey man...' from dual
  7    union all
  8    select '112233xxxyyy' from dual
  9    )
 10  select val from data
 11  where regexp_like(val,'^[[:digit:]]{6}($| .)')
 12  /
VAL
-----------------
112233
112233 hey man...

2 rows selected.

Regards
Michel

[Updated on: Thu, 07 May 2009 08:57]

Report message to a moderator

Re: regular expression with owa pattern [message #402081 is a reply to message #402076] Thu, 07 May 2009 09:07 Go to previous messageGo to next message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Thanks,
but:
- is it possible without using "regexp_like"?

I need to put in this form:
declare 
  rt_vc_arr owa_text.vc_arr;
  s_message varchar(20) := '123456asdf';
begin
  IF owa_pattern.match(line =>s_message,                         
                        pat => ???
                        backrefs => rt_vc_arr,
                        flags   => 'i')
    THEN ......
  ELSE
    ....
  END IF;                             
end;



Thanks for reply.
jan
Re: regular expression with owa pattern [message #402084 is a reply to message #402070] Thu, 07 May 2009 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>- is it possible without using "regexp_like"?
Yes, but why disregard a solution that works?
Does the homework assignment want a different solution?
Re: regular expression with owa pattern [message #402094 is a reply to message #402081] Thu, 07 May 2009 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare 
  2    rt_vc_arr owa_text.vc_arr;
  3    s_message varchar(20) := '123456asdf';
  4  begin
  5    IF regexp_like(s_message,'^[[:digit:]]{6}($| .)')
  6      THEN null;
  7    ELSE
  8      null;
  9    END IF;                             
 10  end;
 11  /

PL/SQL procedure successfully completed.

regexp_like works in PL/SQL, owa_pattern is an old stuff, use features that are available and efficient in your version, don't use old and inefficient ones.

Regards
Michel

[Updated on: Mon, 11 May 2009 01:58]

Report message to a moderator

Re: regular expression with owa pattern [message #402490 is a reply to message #402070] Mon, 11 May 2009 01:47 Go to previous messageGo to next message
jsulc
Messages: 19
Registered: October 2005
Junior Member
To: Michel Cadot
Thank you very much for your help.
I didn't know its an "old stuff", ok - I will switch to "regexp_like". I like it better anyway.
Thank you.
Jan

To: BlackSwan
1. There are often more working solutions to the problem and still, there is a good reason not to use some of them.
2. If you blame me for misusing this great forum with homework-issues, you should have some good reason to do that. Otherwise, please, do not post useless comments like this.
Re: regular expression with owa pattern [message #402492 is a reply to message #402490] Mon, 11 May 2009 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a small test (of course the pattern in owa_pattern is not correct but it does not matter for the test), executing 100000 the same thing in both case:
SQL> declare 
  2    rt_vc_arr owa_text.vc_arr;
  3    s_message varchar(20) := '123456asdf';
  4    now integer;
  5  begin
  6    now := dbms_utility.get_time();
  7    for i in 1..100000 loop
  8      s_message := dbms_random.string ('a', 10);
  9      IF owa_pattern.match(line =>s_message,                         
 10                            pat => '^\d{6}[\s\$]',
 11                            backrefs => rt_vc_arr,
 12                            flags   => 'i')
 13        THEN null;
 14      ELSE
 15        null;
 16      END IF;                             
 17    end loop;
 18    dbms_output.put_line('owa_pattern: '||((dbms_utility.get_time()-now)/100)||'s');
 19    now := dbms_utility.get_time();
 20    for i in 1..100000 loop
 21      s_message := dbms_random.string ('a', 10);
 22      IF regexp_like(s_message,'^[[:digit:]]{6}($| .)')
 23        THEN null;
 24      ELSE
 25        null;
 26      END IF;                             
 27    end loop;
 28    dbms_output.put_line('regexp_like: '||((dbms_utility.get_time()-now)/100)||'s');
 29  end;
 30  /
owa_pattern: 27.11s
regexp_like: 2.51s

PL/SQL procedure successfully completed.

This was in 10.2 and regexp functions have been greatly improved in 11g.

Regards
Michel

[Updated on: Mon, 11 May 2009 02:15]

Report message to a moderator

Re: regular expression with owa pattern [message #402513 is a reply to message #402492] Mon, 11 May 2009 04:35 Go to previous message
jsulc
Messages: 19
Registered: October 2005
Junior Member
Thank you - very nice.

Can I have one more question, please?

OWA_PATTERN package has one nice feature, which is advantageous for me - it saves "matched pieces" into "backrefs".

If I don't use owa_pattern - as you recommended, - I can't filter the input string and at the same time to save "matched pieces".

Is the right way firstly filter the input by REGEXP_LIKE and then to cut the relevant pieces one by one using SUBSTR function?

Thank you.

If needed, this is my real code:

Input strings I get: 'ABCDEF 190 85' or 'ABC 190 85 xxxyyy'

And I want to:
1. check the syntax (it must be "string of letters or #" + space + number + space + number + (end of string OR space and then anything))
2. save those two numbers 160 and 685.

Which is:
declare 
  rt_vc_arr owa_text.vc_arr;
  s_mess_body varchar(20) := 'ABCDEF 160 685';
begin
  IF owa_pattern.match (line   => s_mess_body, 
                        pat      => '^[A-Za-z#]+\s(\d+)\s(\d+)[\s\$]',
                        backrefs => rt_vc_arr,
                        flags    => 'i')
    THEN dbms_output.put_line('rt_vc_arr(1) '||rt_vc_arr(1));
         dbms_output.put_line('rt_vc_arr(2) '||rt_vc_arr(2));         
  ELSE
    dbms_output.put_line('nothing found!!!'); 
  END IF;                             
end;


(But this pattern doesn't work - neither dollar sign nor \s works at the context of square brackets.
Neither on Oracle 10.1., nor on 11.1.)

jan
Previous Topic: ORA-14074: partition bound must collate higher than that of the last partition
Next Topic: Performance of the Procedure
Goto Forum:
  


Current Time: Thu Dec 08 03:59:52 CST 2016

Total time taken to generate the page: 0.12403 seconds