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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 14 Mar 2004 10:03:08 +0100
Message-ID: <40541f98$0$278$636a15ce@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
>
>

Here's another solution with almost only SQL. Still with my name table:
create table name (id number(5), name varchar2(20)); 213 first names insert into it

SQL> create or replace function rev_string (string in varchar2, reverse in number default 0)   2 return varchar2
  3 is

  4     i   binary_integer;
  5     lg  binary_integer;
  6     str varchar2(2000);
  7  begin
  8     if reverse = 0 then return string;
  9     else
 10        str := '';
 11        lg  := length(string);
 12        for i in reverse 1..lg loop
 13           str := str || substr(string,i,1);
 14        end loop;
 15        return str;
 16     end if;

 17 end;
 18 /

Fonction créée.

SQL> select /*+ NO_MERGE(v) */ name,

  2         dbms_random.string('U',bef)||
  3         rev_string(name,round(dbms_random.value))||
  4         dbms_random.string('U',15/*total_lg*/-bef-lg) res
  5  from ( select id, name, length(name) lg,
  6                round(dbms_random.value*(15/*total_lg*/-length(name))) bef
  7         from ( select round(dbms_random.value(1,213/*words_nb*/)) nb
  8                from (Select 1 from dual group by cube(1,2,3,4))
  9                where rownum <= 15 /*line_nb*/ ),
 10              name
 11         where id = nb ) v

 12 order by dbms_random.value
 13 /

NAME                 RES
-------------------- ------------------------------
SUNNY                PRMYNNUSTJMYUXS
NICOLAS              DSALOCINWAPCTSM
MAXENS               YIOBFXSUUSNEXAM
NAJIB                ODMFPOQSBIJANGL
VALENTIN             UWBTKIVALENTING
TIMON                HTIMONYQFFDGAPK
ELYA                 ENHPAYLEPTLNEVY
ANTTON               EOLQWXCCANTTONJ
SOHANE               LBINAFTWHENAHOS
CORENTIN             RKBHURNITNEROCL
MAEVA                HMAEVAGJKAUHSTC
INES                 BNQHSENITZTCORO
CORENTIN             BJMCORENTINFSML
LEYNA                XQZANYELUAVHTJH
OCEAN                POAFOCEANQRKXNH

15 ligne(s) sélectionnée(s).

Regards
Michel Cadot Received on Sun Mar 14 2004 - 03:03:08 CST