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

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:]]*',''); 

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))

Since you're comfortable with regular expressions, you probably want to use the REGEXP_REPLACE function. If you want to eliminate anything that matches the [:space:] POSIX class

REGEXP_REPLACE( my_value, '[[:space:]]', '' )SQL> edWrote file afiedt.buf  1  select '|' ||  2         regexp_replace( 'foo ' || chr(9), '[[:space:]]', '' ) ||  3         '|'  4*   from dualSQL> /'|'||-----|foo|

If you want to leave one space in place for every set of continuous space characters, just add the + to the regular expression and use a space as the replacement character.

with x as (  select 'abc 123  234     5' str    from dual)select regexp_replace( str, '[[:space:]]+', ' ' )  from x