Re: Deleting or Trimming White Spaces

My recursive function works for me
 create or replace function F_SPACES( in_string varchar2)  return varchar2
 is
   lcl varchar2(2000);
   double_space varchar2(2) := ' ';
   single_space varchar2(1) := ' ';
   BEGIN
   lcl :=replace(in_string,double_space,single_space);

    if instr(lcl,double_space) > 0 then      lcl := f_spaces(lcl);
   end if;

   return(lcl);
   END;
/
"DominiqS" <dominiqs_at_o2.co.uk> wrote in message news:1042207286.105147_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.
>
> I have tried the ltrim function as below
>
> I tried to figure an easy or quick way of resolving this problem using the
> the ltrim functions to get rid of the leading spaces but still no joy,
.
> I created a copy of the table using the command
> create test_company as select name from company where name like '% %' ;
>
> then i tried
>
> update test_company
> set name = ltrim(name, ' ' ) ;
>
> but still no joy.The spaces still exist.
>
>
> 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
>
>
Received on Fri Jan 10 2003 - 21:38:40 CST