Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Deleting Double spaces in a Field
zp wrote:
> Remember that this will only catch double spaces, not triple ect.
> You would have to run it several times if you also have triple spaces etc.
>
> zp
>
> "Peter van Rijn" <p.vanrijnREMOVE_at_THISzhew.nl> wrote in message
> news:v1tmjhplau05e9_at_corp.supernews.com...
> > update company
> > set name=replace(name, ' ', ' ')
> >
> > hth,
> > Peter
> >
> > "DominiqS" <dominiqs_at_o2.co.uk> schreef in bericht
> > news:1042202523.837019_at_ernani.logica.co.uk...
> > > All,
> > > Quick question how do i delete a double blank space from a table called
> > > company and the field or row where the blank spaces need to be deleted
> is
> > > name ie company name .
> > > There is an extra blank space in between the company names eg
> > > My Own Company (one with double space)
> > > My Own Company (one with single space) normal.
> > > I have run a sql to identify the 166 rows that have this problem now i
> > need
> > > a quick way of resolving this problem ie deleteing the extra space from
> > > these rows.
> > > Any help would be greatly appreciated.Thanks
> > > We are running Oracle 817 on Solaris 8.
> > >
> > > I have about 166 rows affected by this double space problem,Is there a
> > fast
> > > way of doing it or even a script to take care of this
> > >
> > >
> >
> >
If you want to do this and cover all bases use a FOR loop that decrements from some large number of spaces to 2 removing any it finds. For example:
FOR i IN REVERSE 2 .. 100
<remove the spaces here where the number of spaces = i> END LOOP; Daniel Morgan Received on Sun Jan 12 2003 - 16:39:35 CST