Re: Deleting or Trimming White Spaces - addendum

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 19 Jan 2003 12:05:39 GMT
Message-ID: <b0e4aj$nd85p$3@ID-82536.news.dfncis.de>

>> >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.
 

 [..]  

> 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_;

Hello, I like your solution. Still, can you also give an example that replaces three or more consecutive spaces:

create table test_ (company_ VARCHAR2(50));

insert into test_ values ('My Own  Company');
insert into test_ values ('His Company');
insert into test_ values ('  Her  Company ');
insert into test_ values ('  Bruno''s  Company  in  Madrid');
insert into test_ values ('  Heidi''s    Company  in    London  ');

commit;

select replace(ltrim(rtrim(company_)), ' ', ' ') from test_;

drop table test_;

Output =>

REPLACE(LTRIM(RTRIM(COMPANY_)),'','')

---

My Own Company
His Company
Her Company
Bruno's Company in Madrid
Heidi's Company in London

... There are still spaces in Heidi's company.

Thanks for any pointer

Rene

-- 
  no sig today

Received on Sun Jan 19 2003 - 06:05:39 CST