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 Go to next message
Michel Cadot
Messages: 58851
Registered: March 2007
Location: Nanterre, France, http://...
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 335 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #291231 is a reply to message #290878] Thu, 03 January 2008 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58851
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
Frank
Messages: 7877
Registered: March 2000
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 Go to previous messageGo to next message
Michel Cadot
Messages: 58851
Registered: March 2007
Location: Nanterre, France, http://...
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 #291341 is a reply to message #290789] Thu, 03 January 2008 23:16 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And how come my answer is close to Actual code ? I have never used the field 'ID' in the table names.

Quote:
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.


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 Smile .

Rajuvan.

[Updated on: Sat, 12 February 2011 09:06] by Moderator

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 Go to previous messageGo to next message
Michel Cadot
Messages: 58851
Registered: March 2007
Location: Nanterre, France, http://...
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! ./fa/1581/0/

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 Go to previous message
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)

Previous Topic: Puzzle n°04 - Evenly share batches of articles into groups ***
Next Topic: Oracle Database creation 10g
Goto Forum:
  


Current Time: Thu Aug 21 03:17:07 CDT 2014

Total time taken to generate the page: 0.13942 seconds