Re: Deleting or Trimming White Spaces

LTRIM and RTRIM and after 8i, TRIM only deal with spaces before or after the field's contents - not within it so you will need to try a combination of substr,instr,ltrim and concatenation to do it, maybe like this:

name = substr(name,1,instr(name,' ',1,2)) || trim(substr('name',instr(name,' ',1,3))) from dual

If version is earlier than 8i the use ltrim(rtrim( in place of trim

hth,

Please check my
"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
>
Received on Fri Jan 10 2003 - 09:08:36 CST