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: 17653
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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 56 time(s))
[Updated on: Tue, 01 January 2008 01:08]
|
|
|
| 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: 1075
Registered: May 2005
Location: Bangalore |
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: Tue, 01 January 2008 23:05]
|
|
|
| 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: 1075
Registered: May 2005
Location: Bangalore |
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: Wed, 02 January 2008 00:08]
|
|
|
| Re: Puzzle n°02 - Hiding names in crossword array ** [message #291231 is a reply to message #290878 ] |
Thu, 03 January 2008 08:04 |
Michel Cadot
Messages: 17653
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
Did you find it yourself?
Regards
Michel
|
|
|
| Re: Puzzle n°02 - Hiding names in crossword array ** [message #291305 is a reply to message #291231 ] |
Thu, 03 January 2008 14:29 |
Frank
Messages: 5756
Registered: April 2002
Location: NL |
Senior Member |
|
|
well, that's quite rude.. (although I don't think you mean it that way)
If you don't expect people to come up with solutions, there is no point in posting any puzzles..
|
|
|
| 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: 17653
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
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]
|
|
|
| Re: Puzzle n°02 - Hiding names in crossword array ** [message #291324 is a reply to message #290789 ] |
Thu, 03 January 2008 22:23 |
|
rajavu1
Messages: 1075
Registered: May 2005
Location: Bangalore |
Senior Member |
|
|
Frank,
I am not surprised to see Michel's rude answer , as it is not the first time he is replying like this. It is unique nature ever since the beginning (At least towards me)  .
Coming to the point, I sincerely admit that my post to Puzzle n^00 is a Deliberate attempt copy and paste (Not clever enough to deceive Maaarten ). What i did , I searched in Google and found this solution which was missed in Michel's Answer . So I made it to look like I got it from SQL Prompt . Trust me , I don't even know (even now) the proper usage of MODEL .(Even I don't have access to Oracle 10g DB ). My only intention was to include the usage of MODEL which was missed out in the Topic , which I believe is purpose of the Puzzle Forum ( Members : Please Correct me if I am wrong).
Then my doubt is why Michel asked for ,
| Quote: |
The purpose of this puzzle is to wrap-up all SQL or PL/SQL techniques to generate rows.
Enjoy!
Regards
Michel
|
In the same Puzzle .
And I left my company before Maarten's reply (Though you could see me on-line 24/7 ).
And trust me , I made the query myself for this thread . I felt bad on a senior member asking the question like this , though I admire his brilliancy .
Rajuvan.
[Updated on: Thu, 03 January 2008 22:42]
|
|
| |
| Re: Puzzle n°02 - Hiding names in crossword array ** [message #291358 is a reply to message #291324 ] |
Fri, 04 January 2008 00:11 |
Michel Cadot
Messages: 17653
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| Quote: |
I am not surprised to see Michel's rude answer , as it is not the first time he is replying like this. It is unique nature ever since the beginning (At least towards me)
|
Nothing personal, like you said I have a direct style and don't know what is polically correct. I'm pretty sure you didn't read my posts to DreamzZ when he arrived.
| 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
|
|
|
| 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: 68
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: Thu Jul 24 15:15:26 CDT 2008
Total time taken to generate the page: 0.05124 seconds |
]
Members
Search
Help
Register
Login
Home
