remove Double or more spaces In string

I would like to remove Double spaces or more with a single space.

I've tried using the replace and its just not working. How do I do that ?

i.e has 23 spaces
Select 'LIONEL (FRIEND)' As Fname
Should be Select 'LIONEL (FRIEND)' As Fname

I don't know what database/version you are using.
But if Oracle 10g there is a new regular expresion functions that will help.

Code:

---

sql  > create table t1 (colspaces char(100));
Table created.

sql  > insert into t1 values ('1 2  3  4    5    6      7      ');
1 row created.

sql  > commit;
Commit complete.

here is a regular select showing the spaces in
between each number.
basically 1 has one space, 2 has two spaces, ...
sql  > select 'beg:'||colspaces||':end' string from t1;

STRING
------------------------------------------------------------------------------------------------------------
beg:1 2  3  4    5    6      7                                                                    :end


to replace all spaces with nothing use :
sql  > select 'beg:'||REGEXP_REPLACE(colspaces,' ')||':end' string from t1;

STRING
-------------------------------------------------------------------------------------------------------------

beg:1234567:end

ro replace all multiple spaces with one space use :
sql  > select 'beg:'||REGEXP_REPLACE(colspaces,'  *',' ')||':end' string from t1;

STRING
-----------------------------------------------------------------------------------------------------------------

beg:1 2 3 4 5 6 7 :end

and to get rid of that trailing space :
sql  > select RTRIM('beg:'||REGEXP_REPLACE(colspaces,'  *',' '))||':end' string from t1;

STRING
-----------------------------------------------------------------------------------------------------------------------------

beg:1 2 3 4 5 6 7:end

---

Nothing like that exists in SQL 2000, you have to write a user defined function.

Is there similar I can use in Oracle 9i for REGEXP_REPLACE??