Re: Comparing strings whilst ignoring some characters

Mark,

    Use TRANSLATE to convert the characters you don't want to whitespace and then use REPLACE to remove the whitespace.

  1 select 'the 9 cats', replace(translate('the 9 cats', '0123456789', ' '), ' ')
  2* from dual
SQL> / 'THE9CATS' REPLACE
---------- -------
the 9 cats thecats

-- 
Daniel W. Fink
http://www.optimaldba.com

IOUG-A Live! April 27 - May 1, 2003 Orlando, FL
   Sunday, April 27 8:30am - 4:30pm - Problem Solving with Oracle 9i SQL
   Wednesday, May 1 1:00pm - 2:00pm - Automatic Undo Internals


Mark Richard wrote:



>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: Daniel W. Fink
  INET: optimaldba_at_yahoo.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).