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

Question

I need to trim New Line (Chr(13) and Chr(10) and Tab space from the beginning and end of a String) in an Oracle query. I learnt that there is no easy way to trim multiple characters in Oracle. "trim" function trims only single character. It would be a performance degradation if i call trim function recursivelly in a loop using a function. I heard regexp_replace can match the whitespaces and remove them. Can you guide of a reliable way to use regexp_replace to trim multiple tabspaces or new lines or combinations of them in beginning and end of a String. If there is any other way, Please guide me.

Answer 1

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_2 from     (     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.

Answer 2

This how I would implement it:

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