Solved

Oracle trim whitesapce from the end of a string

Posted on 2014-09-17
4
393 Views
Last Modified: 2014-09-18
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
0
Comment
Question by:fpkeegan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40328319
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
 

Author Comment

by:fpkeegan
ID: 40328531
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40328612
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40329767
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question