The attached code will list the owner, table, columns containing trailing spaces. This is good for data cleanup (used a lot in data warehousing). You can switch it easily to see the ones with leading spaces.
set linesize 300
drop table t_trailing;
create table t_trailing
(owner varchar2(30)
,table_name varchar2(30)
,column_name varchar2(30)
)
storage(initial 64K next 64K pctincrease 0)
pctfree 0 pctused 80;
spool c:\clients\pg\trailing_char_check.sql
— create the insert statements
select ‘insert into t_trailing’||chr(10)||
‘select ”’||owner||”’ owner,”’||table_name||”’ table_name,”’||
column_name||”’ column_name from ‘||owner||’.’||table_name||chr(10)||
‘where substr(‘||column_name||’,-1,1) = ” ” and rownum =1;’
from dba_tab_columns
where owner not in (‘SYS’,’SYSTEM’)
and data_type = ‘CHAR’
and data_length > 1;
spool off
— run the insert statements
@c:\clients\pg\trailing_char_check.sql
— see the results
break on owner skip 1 on table_name
spool c:\clients\pg\columns_with_trailing_blanks.lst
select owner,table_name,column_Name from t_trailing order by owner,table_name;
spool off
drop table t_trailing;