11 Replies Latest reply: Feb 28, 2013 5:46 AM by chris227 RSS

    REGEXP_REPLACE

    N.
      Hi,

      I'm trying to get rid of all spaces between numbers.

      The following query achieves this but it's putting a space between the number and full-stop (e.g '3 .4' and '6 .7')

      SELECT REGEXP_REPLACE(REGEXP_REPLACE ('123.456.789 123 456 ', '(\d) +(\d*?)', '\1\2'),'(\d+)','\1 ') FROM DUAL


      *123 .456 .789123456*

      Does anybody know how to prevent the space before the full-stop?

      Thank's

      N.

      Edited by: N. on Feb 27, 2013 5:40 PM
        • 1. Re: REGEXP_REPLACE
          Frank Kulash
          Hi,
          N. wrote:
          Hi,

          I'm trying to get rid of all spaces between numbers.

          The following query achieves this but it's putting a space between the number and full-stop (e.g '3 .4' and '6 .7')

          SELECT REGEXP_REPLACE(REGEXP_REPLACE ('123.456.789 123 456 ', '(\d) +(\d*?)', '\1\2'),'(\d+)','\1 ') FROM DUAL


          *123 .456 .789123456*

          Does anybody know how to prevent the space before the full-stop?
          The outer REGEXP_REPLACE is adding that space.
          Lose the outer REGEXP_REPLACE, and you won't have the space.
          SELECT  REGEXP_REPLACE ( '123.456.789 123 456 '
                           , '(\d) +(\d*?)'
                           , '\1\2'
                           )     AS spaces_out
          FROM    dual
          ;
          Output:
          SPACES_OUT
          -----------------
          123.456.789123456
          Is one row of sample data really a thorough test?
          Do you need to test for spaces between non-digits, or between a digit and something else? How about single digits separating spaces?
          Whatever sample data you use, post the exact results you want from that data.
          • 2. Re: REGEXP_REPLACE
            Manik
            Dont really understand why you are using regexp, if its just a matter of repmoving spaces from the string/number. Are you trying to do this?
            SELECT REPLACE ('123.456.789 123 456 ', ' ') FROM DUAL;
            output:
            123.456.789123456
            Cheers,
            Manik.
            • 3. Re: REGEXP_REPLACE
              N.
              Hi,

              Thank's for your input.

              Apologies I should have been more specific:

              I need to remove any spaces between numbers but preserve any text in a string.

              The following query almost does this but put's spaces before full-stops (' .').

              SELECT REGEXP_REPLACE(REGEXP_REPLACE('123.456.789 123 4 5 6 The Quick Brown Fox*$!$ 89 90 78 67','(\d) +(\d*?)', '\1\2'),'(\d+)','\1 ') FROM DUAL

              123 .456 .789123456 The Quick Brown Fox*$!$ 89907867

              Thanks.

              N.

              Edited by: N. on Feb 27, 2013 7:31 PM
              • 4. Re: REGEXP_REPLACE
                Manik
                Check this:
                SELECT REGEXP_REPLACE (
                          REGEXP_REPLACE (
                             '123.456.789 123 4 5 6 The Quick Brown Fox*$!$ 89 90 78 67',
                             '(\d) +',
                             '\1\2'),
                          '([[:digit:]])([[:alpha:]])',
                          '\1 \2')
                  FROM DUAL;
                Cheers,
                Manik.
                • 5. Re: REGEXP_REPLACE
                  jeneesh
                  Why not simple REPLACE?
                  replace(your_column,' ')
                  • 6. Re: REGEXP_REPLACE
                    N.
                    Manik,

                    Thank's very much for this!. We're nearly there! Just one little thing:

                    SELECT REGEXP_REPLACE (
                    REGEXP_REPLACE (
                    '123.456.789 123 4 5 6 (The Quick Brown Fox*$!$) 89 90 78 67',
                    '(\d) +',
                    '\1\2'),
                    '([[:digit:]])([[:alpha:]])',
                    '\1 \2')
                    FROM DUAL;

                    123.456.789123456(The Quick Brown Fox*$!$) 89907867

                    *** It doesn't maintain the space between the '6' and '(' ****

                    Any ideas?

                    Thanks again!
                    N.

                    Edited by: N. on Feb 27, 2013 9:44 PM
                    • 7. Re: REGEXP_REPLACE
                      Manik
                      Just extending it..
                      SELECT REGEXP_REPLACE (
                                REGEXP_REPLACE (
                                   '123.456.789 123 4 5 6 (The Quick Brown Fox*$!$) 89 90 78 67',
                                   '(\d) +',
                                   '\1\2'),
                                '([[:digit:]])([[:punct:]])([[:alpha:]])',
                                '\1 \2\3')
                                str
                        FROM DUAL;
                      OUTPUT:
                      123.456.789123456 (The Quick Brown Fox*$!$) 89907867
                      Cheers,
                      Manik.
                      • 8. Re: REGEXP_REPLACE
                        chris227
                        In the solution given the \2' in the first regexp_replace is needless. There may be also cases where a blank is inserted, though there wasnt any before.
                        May be this is simpler
                        with data as (
                        select ' 1 23.456.789 123  4 5 6 (The Quick Brown Fox*$!$) 8 9 90 78 67' s from dual union all
                        select ' 1 23.456.789 123  4 5 6(The Quick Brown Fox*$!$)8 9 90 78 67' from dual union all
                        select ' 1 23.456.789 123  4 5 6 (The Quick Brown Fox*$!$) 8 9 90 78 67 ' from dual union all
                        select '1 23.456.789 123  4 5 6  ( The   Quick Brown Fox*$!$)   8 9 90 78 67 e' from dual
                        )
                        
                        select
                        regexp_replace(
                         regexp_replace(s,'(\d) +(\d)','\1\2')
                         ,'(\d) +(\d)','\1\2') r
                        from data
                        
                        R
                        " 123.456.789123456 (The Quick Brown Fox*$!$) 89907867"
                        " 123.456.789123456(The Quick Brown Fox*$!$)89907867"
                        " 123.456.789123456 (The Quick Brown Fox*$!$) 89907867 "
                        "123.456.789123456  ( The   Quick Brown Fox*$!$)   89907867 e"
                        Edited by: chris227 on 28.02.2013 01:32

                        Edited by: chris227 on 28.02.2013 01:56

                        Edited by: chris227 on 28.02.2013 03:43
                        Replaced with simpler solution
                        • 9. Re: REGEXP_REPLACE
                          N.
                          Manik and chris227, thank you for the input guys. I'm anxious to try these out, will do as soon as I get back to the office.

                          Many thanks again manik.
                          • 10. Re: REGEXP_REPLACE
                            jeneesh
                            MODEL..
                            with t as
                            (
                            select '123.456 . 789 123. 4 5 6 The Quick Brown Fox*$!$ 8 9 90 78 67. ' str
                            from dual
                            )
                            select str_new
                            from t
                            model
                             partition by (str)
                             dimension by (1 rn)
                             measures(str as str_new)
                             rules iterate(10) until regexp_substr(str_new[1],'(\d) *(\.*) +(\.*) *(\d)') is null
                             (
                             str_new[1] = regexp_replace(str_new[1],'(\d) *(\.*) +(\.*) *(\d)','\1\2\3\4')
                             );
                            STR_NEW                                                       
                            ---------------------------------------------------------------
                            123.456.789123.456 The Quick Brown Fox*$!$ 89907867. 
                            • 11. Re: REGEXP_REPLACE
                              chris227
                              I updated my solution with something much simpler.