CODEWITHSUNDEEP
oracle-databaseclob
UltraCommit
UltraCommit

Reputation: 2276

RTRIM(CLOB field) in Oracle PL/SQL

Please suppose I have a CLOB field in an Oracle table, in which I have stored the creation script of a package/procedure/function.

I would like to remove all blanks at the end of every line, but:

a) DBMS_LOB.TRIM (CLOB field) is a procedure and not a function; b) RTRIM (CLOB) don't fail, but does not work to achieve this.

How can I solve my problem?

Please note that the spaces at the beginning of every line are useful for indentation of the PL/SQL source code stored in the CLOB field, so they haven't to be removed.

Thank you in advance for your kind help and cooperation.

Upvotes: 2

Views: 11396

Answers (2)

Howd
Howd

Reputation: 71

If the last line has any trailing spaces then Nick's solution adds an extra line.
It should have been

regexp_replace(col, '\s+('||CHR(10)||'|$)', '\1')

Even so that strips any blank lines out.
Better this solution, but it only deals with trailing spaces and tabs

regexp_replace(col, '[ ' ||chr(9)|| ']+$', '', 1, 0, 'm')

Upvotes: 0

Nick Krasnov
Nick Krasnov

Reputation: 27251

To remove spaces at the end of each line you could use regexp_replace() regular expression function:

regexp_replace(col, '\s+('||CHR(10)||'|$)', chr(10))

Here is an example(Note: replace function is used just to highlight spaces):

with t1(col) as(
  select to_clob('begin  '||chr(10)||chr(32)||chr(32)||'something is going on here'||chr(32)||chr(32)||chr(10)|| 'end'||chr(32)||chr(32))
    from dual
)
select replace(col, ' ', '_') as with_spaces
     , replace(
               regexp_replace(col, '\s+('||CHR(10)||'|$)', chr(10))
               , ' '
               , '_'
               ) as without_spaces
  from t1

Result:

WITH_SPACES                           WITHOUT_SPACES
---------------------------------------------------------------------
begin__                               begin
__something_is_going_on_here__        __something_is_going_on_here
end__                                 end

Upvotes: 3

Related Questions