Results 1 to 4 of 4

Thread: remove Double or more spaces In string

  1. #1
    Join Date
    Sep 2002
    Posts
    218

    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

  2. #2
    Join Date
    Mar 2003
    Posts
    468
    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

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Nothing like that exists in SQL 2000, you have to write a user defined function.

  4. #4
    Join Date
    Dec 2010
    Posts
    1

    RE: remove Double or more spaces In string

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •