This discussion is archived
3 Replies Latest reply: Feb 27, 2013 1:55 AM by 976439 RSS

Remove Space from Column

976439 Newbie
Currently Being Moderated
Hi, just wanted to some help in regards to removing spaces from my username column. I have used the following code to remove the double space to a single space.
regexp_replace(table_a.username, ' {2,}', ' ')
but when looking through my answers I have noticed that there is an extra in some names at the end of the field as well. The initial double space was between first name and last name but now I have to replace the end space completely, but not sure how.

If somebody could please advise.

Thanks in advance.
  • 1. Re: Remove Space from Column
    damorgan Oracle ACE Director
    Currently Being Moderated
    Wrap your REGEXP_REPLACE in TRIM.
  • 2. Re: Remove Space from Column
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    I'm not sure I understand the probleem.
    Do you want to remove a space when it is immediately followed by either
    (a) another space, or
    (b) then end of the string
    ?
    If so
    REGEXP_REPLACE ( table_a.username
                , ' ( |$)'
                , '\1' 
                )
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

    Problems like this sometimes require nested functions, e.g. <tt> REGEXP_REPLACE ( RTRIM ( str ... </tt>
  • 3. Re: Remove Space from Column
    976439 Newbie
    Currently Being Moderated
    Thanks Frank, that did the trick!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points