SQL & PL/SQL

Hi,

I need to trim all white space characters that occur before and after any non white space in a text field.
By white space I mean:
- space chr(32)
- horizontal tab chr(9)
- line feed chr(10)
- carriage return chr(13)

So using this example:

CREATE TABLE test (
id NUMBER,
value VARCHAR2(100));

INSERT INTO test (id, value) values (1, chr(32)||chr(9)||chr(13)||chr(10)||'a'
||' '||'b'||chr(10)||'c'||chr(13)||chr(10)||chr(32)||chr(9)||chr(32));

I need to end up with:

a space b linefeed c

So I want to get rid of the leading and trailing white space, but keep the white space inside the string.

I can't seem to do this with the TRIM function alone. I could do many iterations of replacing the white space with something link '#' and then trimming this, but the white space characters can be in any order.

I tried

REGEXP_REPLACE(value, '[^:space:]')

but that doesn't give the correct result.