Trim Whitespaces (New Line and Tab space) in a String in Oracle

If you have Oracle 10g, REGEXP_REPLACE is pretty flexible.

Using the following string as a test:

chr(9) || 'Q   qwer' || chr(9) || chr(10) ||chr(13) || 'qwerqwer     qwerty' || chr(9) || chr(10) || chr(13)

The [[:space:]] will remove all whitespace, and the ([[:cntrl:]])|(^\t) regexp will remove non-printing characters and tabs.

select    tester,    regexp_replace(tester, '(^[[:space:]]+)|([[:space:]]+$)',null)            regexp_tester_1,    regexp_replace(tester, '(^[[:cntrl:]^\t]+)|([[:cntrl:]^\t]+$)',null)             regexp_tester_2from    (    select        chr(9) || 'Q   qwer' || chr(9) || chr(10) ||                chr(13) || 'qwerqwer     qwerty' || chr(9) ||                 chr(10) || chr(13) tester     from         dual    )

Returning:

• REGEXP_TESTER_1: "Qqwerqwerqwerqwerty"
• REGEXP_TESTER_2: "Q qwerqwerqwer qwerty"

Hope this is of some use.

This how I would implement it:

     REGEXP_REPLACE(text,'(^[[:space:]]*|[[:space:]]*$)')

How about the quick and dirty translate function?

This will remove all occurrences of each character in string1:

SELECT translate(           translate(               translate(string1, CHR(10), '')           , CHR(13), '')       , CHR(09), '') as massagedFROM BLAH;

Regexp_replace is an option, but you may see a performance hit depending on how complex your expression is.