translate the chars you want to ignore to (say) '@'
then replace the @ with null
hth
connor
- Mark Richard <mrichard_at_transurban.com.au> 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: 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).
>
Connor McDonald
web:
http://www.oracledba.co.uk
web:
http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 - 07:28:39 CST