CODEWITHSUNDEEP
oracle-database
Matt
Matt

Reputation: 13

Remove multiple Trailing Spaces and Update in Columns in Oracle

I have an oracle table of M*N Rows and columns which has multiple trailing spaces.

I'm using the below two statements to find the trailing space and update the value. I'm Looking for a script that will work on a table, don't know how many columns the table has, the names of those columns, and which of them are of data type varchar, etc.

Select * From Table Where Column_Name<> TRIM(Column_Name);

UPDATE Table 
set Column_Name= trim(Column_Name)
where Condition=;
commit;

Is there a script where I can update the whole table which has Multiple trailing spaces in one go?

M*N     |Columns|   |   |
------------------------------------
Rows    |Trail  |   |   |
------------------------------------
        |Trail  |   |   |
------------------------------------
        |   |   |   |
------------------------------------
        |   |   |Trail  |
------------------------------------
        |   |Trail  |   |
------------------------------------
        |   |   |   |
------------------------------------
        |   |   |   |
------------------------------------
        |Trail  |   |   |
------------------------------------

Upvotes: 1

Views: 5288

Answers (2)

user5683823
user5683823

Reputation:

Here is a hack for your problem. You will have to work on the procedure if you will use it more than once. In particular I do not handle errors, and I look only for VARCHAR2 columns.

SETUP:

create table tbl ( id number, name varchar2(10), notes varchar2(30) );

insert into tbl values ( 1, 'Joe  ', 'No notes' );
insert into tbl values ( 2, 'Ann'  , 'Spaces  ' );
insert into tbl values ( 3, 'Ben'  , null       );
commit;

select id, '*|'||name||'|*' as name, '*|'||notes||'|*' as notes
from   tbl
;

ID NAME           NOTES                            
-- -------------- ----------------------------------
 1 *|Joe  |*      *|No notes|*                      
 2 *|Ann|*        *|Spaces  |*                      
 3 *|Ben|*        *||*

Notice the name in the first row, and the notes in the second - they have trailing spaces. I added leading *| and trailing |* to show where the trailing spaces are.

The proper way to update this table is

update tbl
  set name = rtrim(name), notes = rtrim(notes)
  where name like '% ' or notes like '% '
;

- notice the WHERE clause, to make sure we don't update rows that don't have any values with trailing spaces. (Updating rows to themselves may seem harmless, but it adds significant overhead, with all the undo and redo they generate.)

So, then, here is PL/SQL code that generates this UPDATE statement dynamically:

create or replace procedure trim_trailing_spaces (
  tbl_name in varchar2,
  own      in varchar2 default null
)
as
  sql_text varchar2(4000)
    := 'update ' || nvl(own, user) || '.' || tbl_name;
  set_text varchar2(1000)
    := chr(10) || '  set   ';
  where_text varchar2(1000)
    := chr(10) || '  where ';
begin
  for r in ( select column_name as col
             from   all_tab_columns
             where  owner       = nvl(upper(own), user)
               and  table_name  = upper(tbl_name)
               and  data_type = 'VARCHAR2'
           )
  loop
    set_text := set_text || r.col || ' = rtrim( ' || r.col || '), ';
    where_text := where_text || r.col || ' like ''% '' or ';
  end loop;

  sql_text := sql_text || rtrim(set_text, ', ') || rtrim(where_text, 'or ');
  execute immediate sql_text;
  commit;
end trim_trailing_spaces;
/

Compile it and then execute it. I pass in the table name 'tbl' (the table we created earlier) and I don't give the optional second argument (for a schema name different from my own), so the updated table will be the one in my schema. Then I execute the SELECT statement from above to see the changes.

exec trim_trailing_spaces('tbl')

select id, '*|'||name||'|*' as name, '*|'||notes||'|*' as notes
from   tbl
;

ID NAME           NOTES                            
-- -------------- ----------------------------------
 1 *|Joe|*        *|No notes|*                      
 2 *|Ann|*        *|Spaces|*                      
 3 *|Ben|*        *||*

So, it seems like it worked.

Upvotes: 1

PiC
PiC

Reputation: 143

The below is the simplest I could think of. Replace dbms_output.put_line with execute immediate if you are satisfied with the output it generates. Uncomment the commit if it makes sense in your case.

BEGIN
   FOR col_i IN (SELECT OWNER, table_name, column_name FROM all_tab_columns WHERE OWNER = 'XXX' AND table_name = 'YYY' AND (data_type LIKE '%CHAR%' OR data_type LIKE '%CLOB') )
   LOOP
      dbms_output.put_line('UPDATE ' || col_i.OWNER || '.' || col_i.table_name ||' set ' || col_i.column_name || ' = LTRIM(' || col_i.column_name || ') WHERE ' || col_i.column_name || ' LIKE '' %''');
--    commit;
   END LOOP;
end;

Upvotes: 0

Related Questions