Oracle PL/SQL : remove "space characters" from a string
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> ed
Wrote file afiedt.buf
1 select '|' ||
2 regexp_replace( 'foo ' || chr(9), '[[:space:]]', '' ) ||
3 '|'
4* from dual
SQL> /
'|'||
-----
|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
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:]]*','');