This discussion is archived
10 Replies Latest reply: Nov 8, 2013 2:41 AM by BluShadow RSS

remove a space/spaces from varchar2 column

731362 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    try this...
    update test1
      set name = replace(name,NULL)
      where name=' ';
    Ranit B.
  • 2. Re: remove a space/spaces from varchar2 column
    damorgan Oracle ACE Director
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    731362 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    731362 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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 Guru Moderator
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points