function to delete extra spaces between words

[jmodic]

I don't think it can be done simply by combining built-in SQL functions. The easiest way is to built your own PL/SQL function taht will remove redundat spaces. The following one is replacing two consecutive spaces with a single space until there is no more "double spaces" in the string.
[code]
create or replace function remove_redundant_spaces (p_instr in varchar2)
return varchar2 is
v_outstr varchar2(4000) := p_instr;
v_length_before number := 0;
v_length_after number := length(v_outstr);
begin
while v_length_before != v_length_after loop
v_length_before := v_length_after;
v_outstr := REPLACE(v_outstr, ' ', ' '); -- replace two blanks with single blank
v_length_after := length(v_outstr);
end loop;
return v_outstr;
end;
/
[code]