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

    Remove Space from Column

    976439
      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
          Wrap your REGEXP_REPLACE in TRIM.
          • 2. Re: Remove Space from Column
            Frank Kulash
            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
              Thanks Frank, that did the trick!