3 Replies Latest reply on Feb 27, 2013 9:55 AM by 976439

    Remove Space from Column

      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
          Wrap your REGEXP_REPLACE in TRIM.
          • 2. Re: Remove Space from Column
            Frank Kulash

            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.,
            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
              Thanks Frank, that did the trick!