Solved

Oracle trim whitesapce from the end of a string

Posted on 2014-09-17
4
371 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
  • 2
4 Comments
 
LVL 76

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Migrating an SQL 2008 database to Oracle 12c 3 91
C# Connection String for Oracle database is not working 22 95
SQL Retrieve Values 4 58
execute immediate plsql block 5 34
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now