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

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: 14 Mar 2004 03:30:00 -0800
Message-ID: <73e20c6c.0403140330.19eb94a@posting.google.com>

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:<405346f0$0$295$626a14ce_at_news.free.fr>...

> 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
> /

Nifty! Did you know these new uses of dbms_random also work in 9ir2 even though they are documented only in the 10g manual?

Before you dismiss SQL to generate the reverse strings:   1 create table my_range as
  2 with zot as (select rownum row_num,keyword from v$reserved_words   3 where length > 3 and length < 9 and keyword != ' ')   4 select keyword
  5 from zot
  6 where rownum < 16
  7* and mod(row_num,14) = 12
Purr>/
Table created.
Elapsed: 00:00:00.00
PROMPT Quick and dirty, but it works. A non-existing substr is NULL.

Purr>select substr(keyword,8,1)||substr(keyword,7,1)||
  2         substr(keyword,6,1)||substr(keyword,5,1)||
  3         substr(keyword,4,1)||substr(keyword,3,1)||
  4         substr(keyword,2,1)||substr(keyword,1,1) drowyek,
  5         keyword

  6 from my_range;
Press Return to continue...
DROWYEK KEYWORD

-------- ----------------------------------------------------------------

SWOR_LLA ALL_ROWS
EROFEB BEFORE
KLUB BULK
KCEHC CHECK
TNEMMOC COMMENT
SSORC CROSS
DERREFED DEFERRED
TCNITSID DISTINCT
TPECXE EXCEPT

TSAF     FAST
MORF     FROM
PAEH     HEAP
AVAJ     JAVA
SSEL     LESS
KCOL     LOCK

15 rows selected.
Purr>and so on.

Across is much harder with SQL only. Can't figure it out in the time I've got.
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Sun Mar 14 2004 - 05:30:00 CST