Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Simple SQL question

Re: Simple SQL question

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 1 Feb 2006 10:32:21 -0500
Message-ID: <m9WdnQfBtMsVSX3enZ2dnUVZ_sOdnZ2d@comcast.com>

"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):



create or replace function compact_str(str in varchar2) return varchar2
is
  /*
    compact all multi-space sequences into single spaces     for pre-regular expression versions of oracle   */

  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;

  end repl;

begin
  return repl(str);
end;


usage:

SQL> select compact_str('spaces'||rpad(' ',10)||'and more spaces') as compacted
  2 from dual
  3 /

COMPACTED



spaces and more spaces

done; 4 recursions.

SQL> select compact_str('spaces'||rpad(' ',100)||'and more spaces') as compacted
  2 from dual;

COMPACTED



spaces and more spaces

done; 7 recursions.

SQL> select compact_str('spaces'||rpad(' ',1000)||'and more spaces') as compacted
  2 from dual;

COMPACTED



spaces and more spaces

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US