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

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:40545c28$0$277$626a14ce_at_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... :-)

Ah. I was going to post that sundry personages had apparently forgotten that minor detail!

But it's darn'd good so far regardless.

Regards
HJR Received on Sun Mar 14 2004 - 12:37:20 CST