Home » Other » General » Puzzle n°02 - Hiding names in crossword array **
| Puzzle n°02 - Hiding names in crossword array ** [message #290789] |
Mon, 31 December 2007 15:12  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Maybe you know this game to find words hidden in an array.
The purpose of this puzzle is to create a SQL statement that generates a random array of 15 lines of 15 characters with each line containing a name that have to be randomly read from left to right or right to left.
I join a script to create a table of names (sorry french ones).
Here's the description of the table and an example of the array that can be generated.
SQL> desc names
Name Null? Type
-------------------------------- -------- ----------------------
NOM VARCHAR2(20 CHAR)
ID NUMBER(5)
NOM RES
-------------------- --------------------
EVAN FEVANXRAOLYUJFO
LENA BCXHQANELSIWKOB
ANTHONY KLWYNOHTNAYIGIZ
LENA BDNVMDODLENABPH
MATTYS LUDPWAPMATTYSBP
BASTIEN NEITSABYLOQDNNF
WILLIAMS GOWRQJSMAILLIWO
MAKAN HMWOGXNANAKAMJI
BENJAMIN UOWSMNIMAJNEBSP
JEREMY VHBNXFJYMEREJZP
BERKANT QFJOBTNAKREBRTL
RAYAN ORDQZUWZRAYANTM
LOUIS GLDFLOUISODESOR
UMIT NVFYCUMITWHEELB
LUCAS FBGWRFZRSACULNC
On the right the array, on the left the name that is hidden on the same line.
Enjoy!
Regards
Michel
(I don't know if the title is appropriate, please change it.)
-
Attachment: names.sql
(Size: 9.63KB, Downloaded 2164 times)
[Updated on: Tue, 01 January 2008 01:08] Report message to a moderator |
|
|
|
| Re: Puzzle n°02 - Hiding names in crossword array ** [message #290876 is a reply to message #290789] |
Tue, 01 January 2008 23:04  |
 |
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
|
Senior Member |
|
|
One solution would be ,
WITH DATASET AS (
select Nom,
length(nom) ln,
trunc(dbms_random.value(0,16-length(nom))) X,
trunc(dbms_random.value(0,2))Y from names)
select Nom ,
dbms_random.string('U', x)||
DECODE(Y,0,NoM,REVERSE(Nom))||
dbms_random.string('U', 15 - (LN+X)) RES
from DATASET
Rajuvan.
[Updated on: Sat, 12 February 2011 09:04] by Moderator Report message to a moderator |
|
|
|
| Re: Puzzle n°02 - Hiding names in crossword array ** [message #290878 is a reply to message #290789] |
Wed, 02 January 2008 00:04 |
|
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
|
Senior Member |
|
|
Or Rather ,
SQL> WITH DATASET AS (
2 select Nom,
3 length(nom) ln,
4 trunc(dbms_random.value(0,16-length(nom))) X,
5 trunc(dbms_random.value(0,2))Y
6 from names ORDER BY dbms_random.value())
7 select Nom ,
8 dbms_random.string('U', x)||
9 DECODE(Y,0,NoM,REVERSE(Nom))||
10 dbms_random.string('U', 15 - (LN+X)) RES
11 from DATASET
12 where rownum <=15;
NOM RES
-------------------- ------------------------------
FAOUZI BKVYZAOWPFAOUZI
CELIO OILECBJOBSKKNCG
AMBRE SEKVMTERBMACPIW
TAMERA NAONZMTAMERAMHZ
ROMAIN INNIAMORZRYUEHU
MEDY FLGKOPQVVMYDEMK
LOUISE HVNPLDBVLOUISEB
ENZO IOZNESTXNUEMISP
DAMIAN SFIZDAMIANXLEVJ
ADAMA AKADAMATIUOTQBN
AGATHE ADWBAGATHERSGLW
MATHEW XIFNAVJFWEHTAMG
SYLVIA SLVDAIVLYSLPJDG
DARRYL NOPHFDARRYLQFTH
LAURA KLUWVTARUALKJKW
15 rows selected.
SQL>
Rajuvan.
[Updated on: Sat, 12 February 2011 09:04] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Puzzle n°02 - Hiding names in crossword array ** [message #291306 is a reply to message #291305] |
Thu, 03 January 2008 15:05 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Oh yes alone it seems to be quite rude but it is a follow-up to his answer http://www.orafaq.com/forum/mv/msg/95011/291199/102589/#msg_291199 followed by Maarten comment http://www.orafaq.com/forum/mv/msg/95011/291216/102589/#msg_291216 showing his answer is a copy of wiki.
In addition, this puzzle was posted in newsgroup many years ago (8i was the latest version at this time) and it takes several roundtrips and days to the whole community to get the final answer (very close to this one) from the first answer I gave:
def total_lg=15
def words_nb=213
def line_nb=15
col res format a20
select /*+ NO_MERGE(v) */ nom,
dbms_random.string('U',bef)||
decode(round(dbms_random.value),0,nom,reverse(nom))||
dbms_random.string('U',&total_lg-bef-lg) res
from ( select id, nom, length(nom) lg,
round(dbms_random.value*(&total_lg-length(nom))) bef
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 ),
names
where id = nb ) v
order by dbms_random.value
/
So I was really surprised to see the answer in one shot and a couple of hours but didn't say anything (for the reason you gave) until Maarten's post.
So my question, hoping a sincere answer.
I nevertheless admit that this new answer is better than the old one.
Regards
Michel
[Updated on: Thu, 03 January 2008 15:08] Report message to a moderator |
|
|
|
|
|
| Re: Puzzle n°02 - Hiding names in crossword array ** [message #291358 is a reply to message #290789] |
Fri, 04 January 2008 00:11 |
|
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:If I were in place of Michel , i will definitely appreciate Poster poster for good attempt ( Even i had done it to Michel !! ) instead of suspecting a fowl smell
Sorry, but my english does not allow me to understand this sentence. So I don't know if it is for me, good, bad, or what else.
Anyway, your query if far better than the ancient solution (except in that it works on the whole table (and so the rownum<=15) whereas the old one first chooses the 15 rows and so the use of id). It is in the new style of writing SQL.
If you found it by yourself I say: wow! 
Regards
Michel
[Updated on: Sat, 12 February 2011 09:06] Report message to a moderator |
|
|
|
| Re: Puzzle n°02 - Hiding names in crossword array ** [message #300656 is a reply to message #291358] |
Sun, 17 February 2008 09:23 |
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
|
Member |
|
|
here's a slight variation of ajavu1's
SELECT nom, SUBSTR (str, 1, fig) || rev || SUBSTR (str, fig + 1) res
FROM (SELECT DECODE (TRUNC (DBMS_RANDOM.VALUE (0, 2)), 0, nom, REVERSE (nom)) rev,
nom, DBMS_RANDOM.STRING ('U', 15 - LENGTH (nom)) str,
TRUNC (DBMS_RANDOM.VALUE (0, 15 - LENGTH (nom) + 1)) fig
FROM names
WHERE ROWNUM <= 15)
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 15:18:37 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
.