Re: Comparing strings whilst ignoring some characters

From: <Jared.Still_at_radisys.com>
Date: Mon, 17 Mar 2003 09:32:56 -0800
Message-ID: <F001.0056BC56.20030317093256@fatcity.com>

This is a job for regular expressions, which make this job easy.

Run $ORACLE_HOME/rdbms/admin/pubpat.sql and privpat.sql to create the owa_pattern packages.

Here are some examples I keep around.

Jared

declare

        tstr varchar2(100) := 'this contains tabs               multiple 
spaces      and single spaces';
begin
        dbms_output.put_line( tstr);
        owa_pattern.change( tstr, '\s', '', 'g');
        dbms_output.put_line( tstr);

end;
/

declare

        tstr varchar2(100) := 'this c34ontains s0239everal 2340 numeric 882 dig2its';
begin

        dbms_output.put_line( tstr);
        -- remove the digits
        owa_pattern.change( tstr, '\d', '', 'g');
        dbms_output.put_line( tstr);
        -- remove the extra spaces
        owa_pattern.change( tstr, '\s+', ' ', 'g');
        dbms_output.put_line( tstr);

end;
/

drop table owatest;

create table owatest (

        test varchar2(20)
)
/

insert into owatest values('non numeric row'); insert into owatest values('numeric 23423 row');

commit;

select *
from owatest
where owa_pattern.amatch(test,1,'^.*\d') > 0
/

drop table regex;

create table regex (

        test varchar2(20)
);

create or replace function strip_str (

        data_in varchar2
        --, regex_in varchar2

)
return varchar2
is

        test_str varchar2(4000);
begin

        test_str := data_in;
        --owa_pattern.change(test_str, regex_in, '', 'g');
        owa_pattern.change(test_str, '\x0a', '', 'g');
        owa_pattern.change(test_str, '\x0c', '', 'g');
        owa_pattern.change(test_str, '\x0d', '', 'g');
        return test_str;

end;
/

show error function strip_str

insert into regex values( 'carriage' || chr(13) || 'return');
insert into regex values( 'line' || chr(10) || 'feeds' || chr(10));
insert into regex values( 'form feed' || chr(12));

commit;

select test
from regex;

select strip_str(test) test
from regex
/

"Mark Richard" <mrichard_at_transurban.com.au> Sent by: root_at_fatcity.com
 03/16/2003 09:39 PM
 Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Comparing strings whilst ignoring some characters

Hi Everyone,

I have a common problem and whilst I can remember solving it in the past I'm drawing a mental blank this time...

I need to look for duplicates in a varchar2 field in a table. However I should ignore case, whitespace and non-alphanumeric characters. Obviously upper, rtrim and ltrim get me a long way to the solution, but... What is the easiest way to remove inline space, so that "the cat" equals "thecat". I thought I used the translate function, but a quick inspection didn't reveal how.

Thanks in advance,

          Mark.

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   Privileged/Confidential information may be contained in this message.
          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Richard
  INET: mrichard_at_transurban.com.au

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Mar 17 2003 - 11:32:56 CST