Re: Deleting Double spaces in a Field

Lloyd Sheen wrote:

> Use replace
>
> select replace('This is a test',' ',' ') from dual
> will return
> 'This is a test'
>
> Lloyd Sheen
> "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3E21EEA7.1FA32BBA_at_exesolutions.com...
> > 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
> >

It doesn't handle groups of 3 spaces, 4 spaces, 100 spaces etc. which is what I was addressing.

Daniel Morgan Received on Mon Jan 13 2003 - 01:52:44 CST