Oracle regexp trailing spaces

Question

How do I remove only trailing spaces and chr(10) and chr(13) using Oracle regexp in a sql statement?

Example:

with txt as (select chr(10)||chr(10)||'  Hey Bob   '||chr(10)||chr(13) a  from dual)
select a
      ,regexp_replace(a,chr(10)||'+|'||chr(13)||'+|'||chr(32)||'+$','')
      ,regexp_replace(a,'['||chr(10)||'+'||chr(13)||'+'||chr(32)||'+]$','')
  from txt;

Desired result:

'  Hey Bob'
1. Leading and non-trailing spaces remain
2. Trailing spaces and eol characters removed

ohmu · Accepted Answer · 2014-12-09 18:38:03Z

4

It would be better to use [[:space:]] to capture all whitespace:

regexp_replace(a, '[[:space:]]+$', '')

But if you explicitly want just the new-line (10), carriage-return (13) and space (32) characters, you can do:

regexp_replace(a, '[' || chr(10) || chr(13) || ' ]+$', '')
--                                              ^-- space character.

edited Dec 9, 2014 at 18:38

answered Dec 9, 2014 at 18:32

ohmu

19.9k44 gold badges114 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Over a year ago

Thanks very much - this is what I needed - moving the '+' outside of the '[]'

Adam Over a year ago

I also tried using [:space:] instead of the explicit new-line, etc, and it works perfectly - handles tab characters as well. Thanks!

Collectives™ on Stack Overflow

Oracle regexp trailing spaces

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related