Re: Deleting Double spaces in a Field

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
>
Received on Sun Jan 12 2003 - 19:37:38 CST