Oracle PL/SQL : remove "space characters" from a string

Question

In my Oracle 10g database I would like to remove "space characters" (spaces, tabs, carriage returns...) from the values of a table field.

Is TRANSLATE() the way to go ? For example something like:

MY_VALUE := TRANSLATE(MY_VALUE,   CHR(9) || CHR(10) || CHR(11) || CHR(12) || CHR(13) || ' ', ''); 

Or is there any better alternative (something like [:space:] in PHP PCRE) ?

Thanks for any piece of advice.

Answer 1

I'd go for regexp_replace, although I'm not 100% sure this is usable in PL/SQL

my_value := regexp_replace(my_value, '[[:space:]]*','');  

Answer 2

Shorter version of:

REGEXP_REPLACE( my_value, '[[:space:]]', '' ) 

Would be:

REGEXP_REPLACE( my_value, '\s') 

Neither of the above statements will remove "null" characters.

To remove "nulls" encase the statement with a replace

Like so:

REPLACE(REGEXP_REPLACE( my_value, '\s'), CHR(0))