1) from the above code, we have:
replace( replace( replace(
4 rtrim(
5 rtrim(
6 rtrim(
7 replace( x, 'TEST', '' ),
8 ' ' ),
9 '-' ),
10 ' ' ),
11 ' ', ' @'), '@ ', ''), ' @', ' ')
the rtrim(rtrim(rtrim(replace()))) is getting rid of TEST, trailing spaces and trailing - so....
replace( replace( replace( X ), ' ', ' @'), '@ ', ''), ' @', ' ')
gets rid of multiple spaces (assuming @ is not a valid character in your string...
regexp is not necessary
2) try it out, you don't need to know....
ops$tkyte%ORA11GR2> select replace( replace( replace( X , ' ', ' @'), '@ ', ''), ' @', ' ')
2 from (select 'hello world' x from dual )
3 /
REPLACE(REP
-----------
hello world