14 Replies Latest reply: Sep 10, 2009 4:14 AM by BluShadow RSS

    Trim white space from a text field (I give out answer points like candy!)

    John O'Toole
      Hi,

      I need to trim all white space characters that occur before and after any non white space in a text field.
      By white space I mean:
      - space chr(32)
      - horizontal tab chr(9)
      - line feed chr(10)
      - carriage return chr(13)

      So using this example:
      CREATE TABLE test (
      id NUMBER,
      value VARCHAR2(100));
      
      INSERT INTO test (id, value) values (1, chr(32)||chr(9)||chr(13)||chr(10)||'a'
      ||' '||'b'||chr(10)||'c'||chr(13)||chr(10)||chr(32)||chr(9)||chr(32));
      I need to end up with:
      a space b linefeed c
      So I want to get rid of the leading and trailing white space, but keep the white space inside the string.

      I can't seem to do this with the TRIM function alone. I could do many iterations of replacing the white space with something link '#' and then trimming this, but the white space characters can be in any order.

      I tried
      REGEXP_REPLACE(value, '[^:space:]')
      but that doesn't give the correct result.
        • 1. Re: Trim white space from a text field (I give out answer points like candy
          JustinCave
          I'm sure someone with more regular expression knowledge than I can come up with a more elegant solution, but
          SQL> ed
          Wrote file afiedt.buf
          
            1  select substr( value,
            2                 regexp_instr( value, '(\S)+' ),
            3                 length(value) - regexp_instr( reverse(value), '(\S)+') ) val
          ue
            4*   from test_reggie
          SQL> /
          
          VALUE
          --------------------------------------------------------------------------------
          
          a b
          c
          I'm using REGEXP_INSTR to get the first and last non-white space character in the string and then getting the section of the string between those two points. I'll wager that there is a REGEXP_LIKE expression that would work and require less string parsing but I couldn't get the back references to work out.

          Justin
          • 2. Re: Trim white space from a text field (I give out answer points like candy!)
            Hoek
            Some may laugh, some may cry, but actually works for 'the regexpless'.
            +(tested with only one record)+
            with mask as 
            ( select id 
              ,      translate(lower(value), 'abcdefghijklmnopqrstuvwxyz'
                                           , '$$$$$$$$$$$$$$$$$$$$$$$$$$') transval
              from test
            )
            select test.id
            ,      test.value
            ,      substr( test.value
                         , instr(transval, '$')
                         , length(transval)- instr(transval, '$', -1)
                         ) tadah
            from   mask
            ,      test
            where  mask.id = test.id;
            edit, crying

            Hm, it looks fine in PL/SQL developer, but from SQL*Plus I get:
            MHO%xe> with mask as 
              2  ( select id 
              3    ,      translate(lower(value), 'abcdefghijklmnopqrstuvwxyz'
              4                                 , '$$$$$$$$$$$$$$$$$$$$$$$$$$') transval
              5    from test
              6  )
              7  select test.id
              8  ,      test.value val
              9  ,      substr( test.value
             10               , instr(transval, '$')
             11               , length(transval)- instr(transval, '$', -1)
             12               ) tadah
             13  from   mask
             14  ,      test
             15  where  mask.id = test.id;
            
                    ID VAL   TADAH
            ---------- ----- -----
                     1          a b
                       a b   c
                       c
            back to the drawing board on 9-9-9

            Edited by: hoek on Sep 9, 2009 7:21 PM
            • 3. Re: Trim white space from a text field (I give out answer points like candy!)
              Solomon Yakobson
              Use trim(regexp_replace(value,'\s*(\S+)\s*','\1 ')):
              select  id,
                      '[' || value || ']' val,
                      '[' || trim(regexp_replace(value,'\s*(\S+)\s*','\1 ')) || ']' new_val
                from  test
              /
              
                      ID VAL                            NEW_VAL
              ---------- ------------------------------ ------------------------------
                       1 [                                [a b c]
                         a b
                         c
                               ]
              
              
              SQL> 
              SY.
              • 4. Re: Trim white space from a text field (I give out answer points like candy
                Srini Chavali-Oracle
                I am no SQL expert, but why can't you use RTRIM and LTRIM ?

                MOS Doc 67990.1 - How To Use TRIM, RTRIM, LTRIM Function

                HTH
                Srini
                • 5. Re: Trim white space from a text field (I give out answer points like candy!)
                  Hoek
                  Hi Solomon,


                  Looking at your output, which resembles mine, it makes me wonder:
                  MHO%xe> with mask as 
                    2  ( select id 
                    3    ,      translate(lower(value), 'abcdefghijklmnopqrstuvwxyz'
                    4                                 , '$$$$$$$$$$$$$$$$$$$$$$$$$$') transval
                    5    from test
                    6  )
                    7  select test.id
                    8  ,      '['||test.value||']' val
                    9  ,      '['||substr( test.value
                   10               , instr(transval, '$')
                   11               , length(transval)- instr(transval, '$', -1)
                   12               )||']' tadah
                   13  from   mask
                   14  ,      test
                   15  where  mask.id = test.id;
                  
                          ID VAL                  TADAH
                  ---------- -------------------- --------------------
                           1 [                      [a b
                             a b                  c]
                             c
                                   ]
                  What's going on, how come the 'stripped column' seems to have some kind of leading space(s)?
                  Any clues?
                  • 6. Re: Trim white space from a text field (I give out answer points like candy!)
                    MichaelS
                    SQL>  select rtrim (ltrim (value,
                                         chr (9) || chr (13) || chr (10) || chr (9) || chr (32)),
                                  chr (9) || chr (13) || chr (10) || chr (9) || chr (32)) value
                    from test
                    /
                    VALUE                                                                           
                    --------------------------------------------------------------------------------
                    a b                                                                             
                    c                                                                               
                                                                                                    
                    1 row selected.
                    • 7. Re: Trim white space from a text field (I give out answer points like candy
                      Solomon Yakobson
                      schavali wrote:
                      I am no SQL expert, but why can't you use RTRIM and LTRIM ?
                      Reggie needs to trim whitespace characters on the right and left, which can be done with LTRIM/RTRIM, and replace multiple whitespace characters with a single space between words, which can not be done with LTRIM/RTRIM.

                      SY.
                      • 8. Re: Trim white space from a text field (I give out answer points like candy
                        Solomon Yakobson
                        Solomon Yakobson wrote:
                        schavali wrote:
                        I am no SQL expert, but why can't you use RTRIM and LTRIM ?
                        Reggie needs to trim whitespace characters on the right and left, which can be done with LTRIM/RTRIM, and replace multiple whitespace characters with a single space between words, which can not be done with LTRIM/RTRIM.

                        SY.
                        Oops, I completely misread it. You are right, all he needs is RTRIM/LTRIM. Although it is misleading:
                        I need to trim all white space characters that occur before and after any non white space in a text field_.
                        SY.
                        • 9. Re: Trim white space from a text field (I give out answer points like candy
                          John O'Toole
                          ok, back online now...

                          Justin, that doesn't quite work. If I dump the results of that query I get:
                          Typ=1 Len=8: 97,32,98,10,99,13,10,32

                          So there's a few bits left at the end.
                          Dump of the original value is:
                          Typ=1 Len=14: 32,9,13,10,97,32,98,10,99,13,10,32,9,32
                          • 10. Re: Trim white space from a text field (I give out answer points like candy
                            John O'Toole
                            Use trim(regexp_replace(value,'\s*(\S+)\s*','\1 ')):
                            Thanks for that Solomon.
                            That's seems to do the trick. Not sure how though... don't think I'll ever get my head fully around these regular expressions...
                            • 11. Re: Trim white space from a text field (I give out answer points like candy
                              John O'Toole
                              select rtrim (ltrim (value,
                              > chr (9) || chr (13) || chr (10) || chr (9) || chr (32)),
                              > chr (9) || chr (13) || chr (10) || chr (9) || chr (32)) value
                              from test
                              That works a treat too, thanks
                              • 12. Re: Trim white space from a text field (I give out answer points like candy
                                JustinCave
                                Reggie wrote:
                                ok, back online now...

                                Justin, that doesn't quite work. If I dump the results of that query I get:
                                Typ=1 Len=8: 97,32,98,10,99,13,10,32

                                So there's a few bits left at the end.
                                Dump of the original value is:
                                Typ=1 Len=14: 32,9,13,10,97,32,98,10,99,13,10,32,9,32
                                Yup. I screwed up the arithmetic at the end. SUBSTR takes a start position and a length, not a start position and an end position. I should have done
                                SQL> ed
                                Wrote file afiedt.buf
                                
                                  1  select dump(val)
                                  2    from (
                                  3  select substr( value,
                                  4                 regexp_instr( value, '(\S)+' ),
                                  5                 length(value) -
                                  6                   regexp_instr( reverse(value), '(\S)+') -
                                  7                   regexp_instr( value, '(\S)+' ) +
                                  8                   2 ) val
                                  9    from test_reggie
                                 10* )
                                SQL> /
                                
                                DUMP(VAL)
                                --------------------------------------------------------------------------------
                                
                                Typ=1 Len=5: 97,32,98,10,99
                                Justin
                                • 14. Re: Trim white space from a text field (I give out answer points like candy!)
                                  BluShadow
                                  Actually, is it not just this?
                                  SQL> ed
                                  Wrote file afiedt.buf
                                  
                                    1  select value, dump(value)
                                    2  from (select regexp_replace(value, '^\s*|\s*$|(\s)\s*','\1') as value
                                    3*       from test)
                                  SQL> /
                                  
                                  VALUE
                                  --------------------------------------------------------------------------
                                  DUMP(VALUE)
                                  --------------------------------------------------------------------------
                                  a b
                                  c
                                  Typ=1 Len=5: 97,32,98,10,99
                                  
                                  
                                  SQL>
                                  Edit: Note the first two parts of the regular expression do the equivalent of a trim, but keep it all as one function call. ;)

                                  Edited by: BluShadow on Sep 10, 2009 10:14 AM