Re: Deleting or Trimming White Spaces - addendum

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 10 Jan 2003 21:45:46 -0800
Message-ID: <92eeeff0.0301102145.e55b305@posting.google.com>

TurkBear <john.greco_at_dot.state.mn.us> wrote in message news:<ugot1vcs7beqkbl2a9h8ksjdah9mrnjpda_at_4ax.com>...
> Since you posted this twice I missed Peter van Rijn's more elegant solution using the replace function.
> Altho' playing with substrings,instr and concatenations can be fun
>
> ...
>
>
> "DominiqS" <dominiqs_at_o2.co.uk> wrote:
>
> >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
> >

SQL> create table test_ (company_ VARCHAR2(50));

Table created.

SQL> insert into test_ values ('My Own Company');

1 row created.

SQL> insert into test_ values ('His Company');

1 row created.

SQL> insert into test_ values (' Her Company ');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_;

COMPANY_

---

My Own Company
His Company
  Her Company

SQL> update test_

  2     set company_ = replace(ltrim(rtrim(company_)), '  ', ' ')
  3     where instr(ltrim(rtrim(company_)), '  ', 1, 1) != 0;

2 rows updated.

SQL> select * from test_;

COMPANY_

---

My Own Company
His Company
Her Company

Regards
/Rauf Sarwar Received on Fri Jan 10 2003 - 23:45:46 CST