Re: Trimming Spaces

From: R197509 <ramsunders_at_yahoo.com>
Date: 22 May 2002 01:22:47 -0700
Message-ID: <1e562f83.0205220022.4e0d9fcb_at_posting.google.com>


marksmithy69_at_yahoo.com (Mark) wrote in message news:<13e212f9.0205211054.7c44403e_at_posting.google.com>...
> Hello. I have an Oracle database table which contains a field with
> people's names. My problem is that there should only be unique names
> in this field, but there are cases where the same name is there twice,
> because there is an extra spaces between the first and last name. For
> example, there are cases where the name Eddie Vedder and Eddie Vedder
> appear in the table. My job is to clean this up. Is there a function
> that I could use to do a mass clean-up to get rid of the space in the
> middle? Thank you.
>
> Mark

Hi,
  You could try using the "replace" function.   For e.g.,

         delete delme d1 
         where exists 
              (select 1 
               from delme d2 
               where replace(d1.vc,' ','') = replace(d2.vc,' ','') 
               and d1.rowid < d2.rowid);

   This statement would delete all but one of the records that have the vc field differing only in the number of spaces they contain. This statement doesn't control which of the records is retained though.

Regards,
Ram. Received on Wed May 22 2002 - 10:22:47 CEST

Original text of this message