Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Simple SQL question
Jeremy wrote:
>
> How many times have you seen a subject like that :)
>
> If I want to remove all superfluous spaces from a string, can I do it in
> one statement?
>
> e.g. if to convert
> 'the cat sat on the mat'
> to
> 'the cat sat on the mat'
>
> I could code as a loop until the string contains no ' ' (i.e. 2 spaces)
> but that seems a bit sledgehammer/nut?
>
> i.e.
>
> declare
> l_str varchar2(80) := 'the cat sat on the mat';
> begin
> loop
> exit when instr(l_str,' ') = 0;
> l_str := replace(l_str,' ',' ');
> end loop;
> dbms_output.put_line(l_str);
> end;
> /
>
> That works... but isn't pretty.
On the other hand one could TRANSLATE(somevalue_or_column, 'A ', 'A') and have no procedural code at all.
-- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)Received on Tue Jan 31 2006 - 11:27:49 CST