Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Simple SQL question
"Michel Cadot" <micadot{at}altern{dot}org> wrote in message
news:43e04677$0$25716$636a15ce_at_news.free.fr...
>
> "Andy Hassall" <andy_at_andyh.co.uk> a écrit dans le message de news:
> 7ivvt1998ncc2f7q49r2s6lnc2inqr2l8n_at_4ax.com...
> | On Tue, 31 Jan 2006 15:27:28 -0800, DA Morgan <damorgan_at_psoug.org>
> wrote:
> |
> | >Mahesh Rajendran wrote:
> | >>>>The problem with this approach is you may not know how many layers
> of
> | >>>>replace you need.
> | >>
> | >> Agreed. And it will not work if there is leading space~!.
> | >> Thanks
> | >
> | >Why is this discussion continuing? Translate will work in all
> | >situations and is faster.
> |
> | TRANSLATE doesn't give the result the poster asked for.
> |
> | SQL> select translate('the cat sat on the mat', 'A ', 'A')
> | 2 from dual;
> |
> | TRANSLATE('THECAT
> | -----------------
> | thecatsatonthemat
> |
> | --
> | Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> | http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool
>
> I also wander why this discussion goes on as the Ask Tom thread i posted
> (http://asktom.oracle.com/pls/ask/f?p=4950:61:15900582171083902697:::::)
> titled "Replacing Multiple Blanks by single blanks in text"
> explored this question since 24-Nov-2003 and, imo, i don't see another
> solution
> in pre-10g version that those already posted there.
>
> Regards
> Michel Cadot
>
>
i don't think i've seen this approach suggested (forgive me if i'm wrong):
v_str varchar2(4000);
n_len_p_str number;
n_recursions number := 0; -- for testing
function repl(p_str in varchar2)
return varchar2
is
begin
n_len_p_str := length(p_str);
v_str := replace(p_str,' ',' ');
if length(v_str) = n_len_p_str then dbms_output.put_line('done; ' || n_recursions || ' recursions.'); return v_str; else n_recursions := n_recursions + 1; -- for testing return repl(v_str); end if;
begin
return repl(str);
end;
usage:
SQL> select compact_str('spaces'||rpad(' ',10)||'and more spaces') as
compacted
2 from dual
3 /
COMPACTED
done; 4 recursions.
SQL> select compact_str('spaces'||rpad(' ',100)||'and more spaces') as
compacted
2 from dual;
COMPACTED
done; 7 recursions.
SQL> select compact_str('spaces'||rpad(' ',1000)||'and more spaces') as
compacted
2 from dual;
COMPACTED
done; 10 recursions.
SQL> select compact_str(rpad(' ',4000)) as max_recursions 2 from dual;
MAX_RECURSIONS
done; 12 recursions.
++ mcs Received on Wed Feb 01 2006 - 09:32:21 CST