# Miscellaneous > Ask an Expert >  remove Double or more spaces In  string

## sleezy

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

----------


## jkoopmann

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



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

----------


## skhanal

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

----------


## komasi75

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

----------

