Remove multiple Trailing Spaces and Update in Columns in Oracle

Question

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  |   |   |
------------------------------------

The question is not clear. Are you looking for a script that will work on a table, but you don't know how many columns the table has, the names of those columns, and which of them are of string data type of one kind of another (as opposed to DATE or NUMBER), etc.? That is, are you looking for dynamic SQL? Or do you know the column names and you are just looking for a SQL statement to do the job in one pass over the table? — user5683823
– user5683823, Commented Feb 22, 2018 at 20:47
Also: TRIM will remove both leading and trailing spaces; is that OK? You said you only want to remove trailing spaces. — user5683823
– user5683823, Commented Feb 22, 2018 at 20:49

user5683823 · Accepted Answer · 2018-02-23 00:23:26Z

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.

PiC · Accepted Answer · 2018-02-22 21:28:28Z

0

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;

answered Feb 22, 2018 at 21:28

PiC

14311 bronze badges

1 Comment

user5683823 Over a year ago

This looks ok, but the OP specifically wanted to update the entire table in one pass. In your approach, you are updating each column separately - possibly a performance issue. Note also that to trim trailing spaces you should use RTRIM, but that's a minor issue.

Collectives™ on Stack Overflow

Remove multiple Trailing Spaces and Update in Columns in Oracle

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related