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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sun, 14 Mar 2004 14:21:34 +0100
Message-ID: <40545c28$0$277$626a14ce@news.free.fr>

"Michel Cadot" <micadot{at}altern{dot}org> a écrit dans le message de news:40541f98$0$278$636a15ce_at_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
>

Thanks to Nuno "dirty" reverse way, here my "definitive" solution; only in SQL, works from 8i:

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

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

 15 order by dbms_random.value
 16 /

NAME                 RES
-------------------- --------------------
DANIELLE             MZDBELLEINADBBZ
ANAIS                EQMANAISYKIFZCS
SYLVIA               EKXPZAIVLYSDTAF
ALEXIS               YKDYALEXISLAKEB
YANIS                AFCAZMTRDSINAYM
AMBRE                LAMBREEWECPCNNN
MARLEY               YYZLGYELRAMKBFE
MAEL                 LXEAXLLEAMBQCRE
THEO                 TZNNKVPHSFTHEON
MAITENA              NEMZMAITENAIHBZ
LEYNA                TRLEYNASCGLIDRD
MELISSA              ALZQMELISSABDKI
FRANCE               MLXSMIECNARFDRV
MARGAUX              ACQCCREXUAGRAMF
BERKANT              CLUXBERKANTJEZU

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

Next step up/down... :-)

Regards
Michel Cadot Received on Sun Mar 14 2004 - 07:21:34 CST