10 Replies Latest reply: Nov 8, 2013 4:41 AM by BluShadow RSS

    remove a space/spaces from varchar2 column

    Annas
      Hi

      There are about 50 records which contained space/spaces instead of a null value. How can I remove the white spaces (rows 2,4 and 6)

      Thanks
      Anna

      create table test (no number(2), name varchar2(200));

      insert into test values (10, 'Fisher Paykel');
      insert into test values (20, ' ');
      insert into test values (30, 'Ariston Top loader');
      insert into test values (40, ' ');
      insert into test values (50, 'Samsung front loader');
      insert into test values (60, ' ');
      insert into test values (70, ' ');

      Edited by: Annas on Oct 6, 2012 12:38 AM

      Edited by: Annas on Oct 6, 2012 12:41 AM
        • 1. Re: remove a space/spaces from varchar2 column
          ranit B
          try this...
          update test1
            set name = replace(name,NULL)
            where name=' ';
          Ranit B.
          • 2. Re: remove a space/spaces from varchar2 column
            damorgan
            The TRIM function works really well. Has done so for many many years.

            http://www.morganslibrary.org/reference/builtin_functions.html
            • 3. Re: remove a space/spaces from varchar2 column
              515111
              Hello Annas,

              You can use TRIM (http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions199.htm)

              By default, TRIM removes leading and training spaces on a VARCHAR.

              If you want to update existing record in the table, you can do it using UPDATE/MERGE. But here it is important to know what you want to do. Do you want to keep leading or trailing spaces on non empty names? For example, what if you have [SAMSING front Loader  ]?

              If you want to keep these leading and trailing spaces, and put NULL only when you have empty spaces in the field then you can do something like this:
               UPDATE test
                  SET name = CASE WHEN TRIM(name) IS NULL THEN NULL END
                WHERE TRIM(name) IS NULL
                ; 
              Otherwhise, if you also want to remove the leading and trailing spaces on fields that are not empty (and not only a space character), you can do something like this:
               UPDATE test
                  SET name = CASE WHEN TRIM(name) IS NULL THEN NULL ELSE TRIM(name) END
                ; 
              set null <null>;
              DROP TABLE test1;
              create table test1 (no number(2), name varchar2(30));
              insert into test1 values (10, 'Fisher Paykel');
              insert into test1 values (20, ' ');
              insert into test1 values (30, 'Ariston Top loader');
              insert into test1 values (40, ' ');
              insert into test1 values (50, 'Samsung front loader');
              insert into test1 values (60, ' ');
              
               UPDATE test1
                  SET name = CASE WHEN TRIM(name) IS NULL THEN NULL END
                WHERE TRIM(name) IS NULL
                ;
              
              SELECT * FROM test1;
              
              
              insert into test1 values (50, 'Samsung front loader     ');  
              
               UPDATE test1
                  SET name = CASE WHEN TRIM(name) IS NULL THEN NULL ELSE TRIM(name) END
                ; 
                
              SELECT * FROM test1;
                
                
              
              table TEST1 dropped.
              table TEST1 created.
              1 rows inserted.
              1 rows inserted.
              1 rows inserted.
              1 rows inserted.
              1 rows inserted.
              1 rows inserted.
              3 rows updated.
              NO NAME                         
              -- ------------------------------
              10 Fisher Paykel                  
              20 <null>                         
              30 Ariston Top loader             
              40 <null>                         
              50 Samsung front loader           
              60 <null>                         
              
               6 rows selected 
              
              1 rows inserted.
              7 rows updated.
              NO NAME                         
              -- ------------------------------
              10 Fisher Paykel                  
              20 <null>                         
              30 Ariston Top loader             
              40 <null>                         
              50 Samsung front loader           
              60 <null>                         
              50 Samsung front loader           
              
               7 rows selected 
              • 4. Re: remove a space/spaces from varchar2 column
                Annas
                Hi

                Thanks for the reply.

                I am just wondering whether it could be possible only to update the columns having spaces (not the spaces within the words). Is not recommended to update all the columns due to auditing requirements

                Thanks
                anna
                • 5. Re: remove a space/spaces from varchar2 column
                  515111
                  Yeap! It is possible. Read my previous answer.

                  Something like this should do it :)
                  UPDATE test1
                      SET name = TRIM(name)
                    WHERE TRIM(name) IS NULL 
                    ; 
                  • 6. Re: remove a space/spaces from varchar2 column
                    Annas
                    Hi

                    TRIM function can be used to remove leading and/or trailing spaces. The requirement on this post is different

                    Thanks
                    anna
                    • 7. Re: remove a space/spaces from varchar2 column
                      515111
                      Look at the WHERE clause of the UPDATE statement. You will update only records where TRIM(name) is NULL.

                      So, this means that if in a given record

                      name= 'Hello World' -> your record won't be updated.
                      name = ' ' -> your record will be updated and name will be set to NULL
                      name = ' ' -> your record will be updated and name will be set to NULL
                      name = ' Hello World ' -> your record wion't be updated.

                      If you still don't understand, please tell me what result you expect :)
                      • 8. Re: remove a space/spaces from varchar2 column
                        ranit B
                        Hi Annas,
                        Did you try my approach? Hope that fits your requirement well.

                        Thanks,
                        Ranit B.
                        • 9. Re: remove a space/spaces from varchar2 column
                          31fe9d48-ca18-4bb5-a2aa-055264eac7e6

                          There are about 50 records which contained space/spaces instead of a null value. How can I remove the white spaces (rows 2,4 and 6)

                           

                           

                           

                          For this question answer is

                           

                          select replace(column_name,' ','' ) from table_name;

                          • 10. Re: remove a space/spaces from varchar2 column
                            BluShadow

                            This is an old question which already has a correct answer.

                             

                            DO NOT drag up old thread unnecessarily. Especially when you've provided an incorrect answer.

                             

                            Locking this thread