Re: SQL Serious Challenge # 1 - Probably Not Much Fun

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 13 Mar 2004 18:38:44 +0100
Message-ID: <405346f0$0$295$626a14ce@news.free.fr>

"Howard J. Rogers" <hjr_at_dizwell.com> a écrit dans le message de news:4052f9d0$0$31904$afc38c87_at_news.optusnet.com.au...
>
> "Michel Cadot" <micadot{at}altern{dot}org> wrote in message
> news:4052da6a$0$281$636a15ce_at_news.free.fr...
> >
> >
> > PL/SQL allowed?
>
> Oh, I think it would be practically compulsory, wouldn't it?!
>
> Regards
> HJR
>

Without the constraint of reverse order this is possible with only SQL. create table name (id number(5), name varchar2(20)); 213 first names insert into it.

select name,

       dbms_random.string('U',bef)||name||dbms_random.string('U',15/*total_lg*/-bef-lg) res from ( select name, length(name) lg,

              round(dbms_random.value*(15/*total_lg*/-length(name))) bef
       from ( select round(dbms_random.value(1,213/*words_nb*/)) nb
              from (Select 1 from dual group by cube(1,2,3,4))
       where rownum <= 15 /*line_nb*/ ),
     name

where id = nb)
order by dbms_random.value
/

With maybe reverse order:

set serveroutput on size 100000 format wrap declare

   line_nb   binary_integer := 15; /* Number of lines            */
   total_lg  binary_integer := 15; /* Length of a line           */
   words_nb  binary_integer;       /* Total nb of words in table */
   word_lg   binary_integer;       /* Length of current word     */
   before_lg binary_integer;       /* Length before word         */
   after_lg  binary_integer;       /* Length after word          */
   word      varchar2(100);        /* Current word               */
   rev_word  varchar2(100);        /* Word in reverse order      */
   i         binary_integer;

begin

   /* Initialisations */
   dbms_random.seed (to_number(to_char(sysdate,'SSSSS')));    select count(*) into words_nb from name;    dbms_output.put_line (' ');

   /* Loop on "line_nb" words */
   for rec in ( select name

                from ( select round(dbms_random.value(1,words_nb)) nb
                       from (Select 1 from dual group by cube(1,2,3,4))
                       where rownum <= line_nb ),
                     name
                where id = nb
                order by dbms_random.value ) loop
      word      := rec.name;
      word_lg   := length (word);
      before_lg := round (dbms_random.value * (total_lg-word_lg));
      after_lg  := total_lg - before_lg - word_lg;
      dbms_output.put (dbms_random.string('U',before_lg));
      if round(dbms_random.value)=0 then
         dbms_output.put (word);
      else
         rev_word := '';
         for i in reverse 1..word_lg loop
            rev_word := rev_word || substr(word,i,1);
         end loop;
         dbms_output.put (rev_word);
      end if;
      dbms_output.put_line (dbms_random.string('U',after_lg)||' <- '||word);

   end loop;

end;
/

ONNCAMILLEXMBUO <- CAMILLE
QXUFFQAYHAYTAYN <- YAHYA
JDCGSPBNINOTNAZ <- ANTONIN
ANRLNAMATTIEUVH <- MATTIEU
LJXKAKDMICHELGF <- MICHEL
NNRARIMGQZEAPPD <- MIRA
IIGLEONAMXNMHKG <- LEONA
OFNZDARBHEMLVUI <- BRAD
RBPKMATHEWCURDQ <- MATHEW
HQTNQDYLANXBWIF <- DYLAN
GINAJUSTINEYQTZ <- JUSTINE
VXINITRAMGYOWWW <- MARTIN
GMMTCOHDMAXIMEV <- MAXIME
APPVJTNNOMITLLK <- TIMON
GZMFIUYDARBPSZZ <- BRAD

Regards
Michel Cadot Received on Sat Mar 13 2004 - 11:38:44 CST