Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Comparing strings whilst ignoring some characters

Re: Comparing strings whilst ignoring some characters

From: Daniel W. Fink <>
Date: Mon, 17 Mar 2003 05:23:34 -0800
Message-ID: <>


    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
---------- -------
the 9 cats thecats

Daniel W. Fink

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: -- Author: Daniel W. Fink INET: Fat City Network Services -- 858-538-5051 San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: (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 - 07:23:34 CST

Original text of this message