Oracle trim whitesapce from the end of a string

How do you trim all whitespace from the end of a string.  By whitespace I mean characters space,LF, VT,FF,CR, TAB, HT.
For a complete definition of whitespace see http://en.wikipedia.org/wiki/Whitespace_character

The white space in the string must not be changed.

the test string is

select 'This is a ' || chr(9) ||   'test string from removing traing whitespace ' || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(14)  || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(14) || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(14)  as text_string from dual;

The dump of the test string is
Typ=1 Len=82: 84,104,105,115,32,105,115,32,97,32,9,116,101,115,116,32,115,116,114,105,110,103,32,102,114,111,109,32,114,101,109,111,118,105,110,103,32,116,114,97,105,110,103,32,119,104,105,116,101,115,112,97,99,101,32,9,9,10,32,12,10,32,11,14,9,9,10,32,12,10,32,11,14,9,9,10,32,12,10,32,11,14

The dump of the result should be .
Typ=1 Len=54: 84,104,105,115,32,105,115,32,97,32,9,116,101,115,116,32,115,116,114,105,110,103,32,102,114,111,109,32,114,101,109,111,118,105,110,103,32,116,114,97,105,110,103,32,119,104,105,116,101,115,112,97,99,101
fpkeeganAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
rtrim will work.  It replaces ALL characters found in the set.

Unless I'm missing something, chr(14) isn't in the Wikipedia link you posted as a valid whitespace character.

Assuming you meant chr(13) here are the two examples (I replaced chr(14) with chr(13)).

One using RTRIM and one with regexp_replace.

I added ':' to both ends of the selected strings to show they are 'truncated'.

with mydata as
(select 'This is a ' || chr(9) ||   'test string from removing traing whitespace ' || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(13)  || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(13) || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(13)  as text_string from dual) 
select ':' || rtrim(text_string,' '||chr(9)||chr(10)||chr(11)||chr(12)||chr(13)) || ':' from mydata
/



with mydata as
(select 'This is a ' || chr(9) ||   'test string from removing traing whitespace ' || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(13)  || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(13) || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(13)  as text_string from dual) 
select ':' || regexp_replace(text_string,'[[:space:]]*$') || ':' from mydata
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
You could use RTRIM and manually specify all the characters you want in the 'set' to be trimmed:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions160.htm#i1018967

Or try the regexp class:

select regexp_replace('hello' || chr(9) ,'[[:space:]]*$') from dual;
0
 
fpkeeganAuthor Commented:
The Rtrim will not work, you need to keep removing characters of different types till you get to a valid non white character, it does only one character at a time. it does not do whitespace. That is why I provided a test example.  The regexp does not work either.

select dump(regexp_replace(text_string ,'[[:space:]]*$')) from
(select 'This is a ' || chr(9) ||   'test string from removing traing whitespace ' || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(14)  || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(14) || chr(9)  || chr(9) || chr(10) || ' ' || chr(12) ||  chr(10) || ' ' || chr(11)  || chr(14)  as text_string from dual);
)

result:
Typ=1 Len=82: 84,104,105,115,32,105,115,32,97,32,9,116,101,115,116,32,115,116,114,105,110,103,32,102,114,111,109,32,114,101,109,111,118,105,110,103,32,116,114,97,105,110,103,32,119,104,105,116,101,115,112,97,99,101,32,9,9,10,32,12,10,32,11,14,9,9,10,32,12,10,32,11,14,9,9,10,32,12,10,32,11,14

Please use the test example and the test data to verify you solution before you answer and waste my time.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Please use the test example and the test data to verify you solution before you answer and waste my time.

Sidenote: The members here @EE are NOT stupid, many or most of us have decades of technical experience AND slightwv is one of my favorite members here: always helping and patient, straightforward and a truly nice guy (in my opinion)!
So there is absolutely NO NEED being so RUDE!!! Your behavior is FAR from being polite!!!

Kind regards,
Alex
0
All Courses

From novice to tech pro — start learning today.