Home » Other » General » Puzzle n°11 - Dynamic Reshuffling Of A Given String in SQL (Puzzle)
icon14.gif  Puzzle n°11 - Dynamic Reshuffling Of A Given String in SQL [message #360251] Thu, 20 November 2008 03:41 Go to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

This is a puzzle related to the reshuffling a string using SQL.

INPUT

Any string

OUTPUT

Reshuffled form of string each time of execution of SQL Statement.

EXAMPLE

Input string could be any thing. for Example 'RAJUVAN'

Following could be the result of the SQL statement during 5 executions.

RAVANJU
RAVUNAJ
NUVAJAR
AAURNVJ
UAVARJN


Actually I came across this puzzle accidentally while posting a solution to an issue recently .

have a nice play Smile

Smile
Rajuvan.

[Updated on: Thu, 20 November 2008 03:55]

Report message to a moderator

Re: Puzzle n°11 - Dynamic Reshuffling Of A Given String in SQL [message #360397 is a reply to message #360251] Thu, 20 November 2008 19:32 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Not optimized but semms to work...
CREATE OR REPLACE TYPE t_chr1_tab as table of varchar2(1);
/

CREATE OR REPLACE FUNCTION split_str( p_str IN VARCHAR2 )RETURN t_chr1_tab 
AS
   l_str LONG 
   DEFAULT p_str;
   l_n NUMBER := 1;
   tab1 t_chr1_tab := t_chr1_tab();
BEGIN
   IF LENGTH(p_str) > 0 
   THEN
      -- get individual characters into table
      FOR i IN 1..LENGTH(p_str)
      LOOP
         tab1.extend;
         tab1( tab1.count ) := SUBSTR(l_str, i, 1);
      END LOOP;

   END IF;

   RETURN tab1;
END;
/


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> with x as(select letter, rnd, len, rownum rn
  2              from (select column_value letter, dbms_random.value rnd,
  3                          count(*) over(PARTITION BY 1) len
  4                      from TABLE( split_str('RAVANJU' ) )
  5                      order by 2))
  6  select replace(sys_connect_by_path(letter, ','), ',', '') str1
  7      from x
  8    where rn=len
  9    start with rn=1
 10    connect by prior rn = rn-1;

STR1
--------------------------------------------------------------------------------
UJNVRAA

SQL> /

STR1
--------------------------------------------------------------------------------
AAJRNUV

SQL> /

STR1
--------------------------------------------------------------------------------
RNJUAAV

SQL> /

STR1
--------------------------------------------------------------------------------
RUNAAVJ

SQL> /

STR1
--------------------------------------------------------------------------------
ARAVNJU

SQL>

Re: Puzzle n°11 - Dynamic Reshuffling Of A Given String in SQL [message #360424 is a reply to message #360251] Thu, 20 November 2008 23:01 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Very Nice Andrew .. Smug

Though puzzle was for doing it in Straight SQL , your solution was brilliant.

Let me try the same with SQL by adapting your logic ( with your permission)

SQL> SELECT  REPLACE(SYS_CONNECT_BY_PATH (NM1, '/'),'/') RESHUFF_NAME
  2  FROM    (SELECT X.NM1, LEN, ROWNUM R1
  3                FROM (WITH REC AS (SELECT 'ANDREW' NM FROM DUAL)
  4                      SELECT  SUBSTR (NM, ROWNUM, 1) NM1,
  5                              LENGTH (NM) LEN,
  6                              DBMS_RANDOM.VALUE (1, 2)
  7                      FROM REC
  8                      CONNECT BY LEVEL <= LENGTH (NM)
  9                      ORDER BY 3
 10                      ) X
 11          )
 12  WHERE R1 = LEN
 13  START WITH R1 = 1
 14  CONNECT BY R1 = PRIOR R1 + 1;

RESHUFF_NAME
---------------
ANWEDA

SQL> /

RESHUFF_NAME
---------------
RNADER

SQL> /

RESHUFF_NAME
---------------
DRWNAE

SQL> /

RESHUFF_NAME
---------------
DREAWN

SQL> /

RESHUFF_NAME
---------------
NRDEAN

SQL>


Still expecting some mind blowing solution ...

Smile
Rajuvan.
Re: Puzzle n°11 - Dynamic Reshuffling Of A Given String in SQL [message #360436 is a reply to message #360251] Fri, 21 November 2008 00:03 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Another Solution

SQL> WITH T AS
  2       (SELECT 'ANDREW' STR
  3          FROM DUAL)
  4  SELECT  SUBSTR (STR, LEN1 + 1) || REVERSE (SUBSTR (STR, 1, LEN1)) STR1
  5            FROM (SELECT STR, LENGTH (STR) L,
  6                         ROUND (DBMS_RANDOM.VALUE (0, LENGTH (STR))) LEN1
  7                    FROM T)
  8  ;

STR1
------------
WERDNA

SQL> /

STR1
------------
REWDNA

SQL> /

STR1
------------
WERDNA

SQL> /

STR1
------------
NDREWA

SQL> /

STR1
------------
WERDNA

SQL>


Re: Puzzle n°11 - Dynamic Reshuffling Of A Given String in SQL [message #360459 is a reply to message #360436] Fri, 21 November 2008 02:02 Go to previous message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

more satisfactory if you use reverse function and dbms_random package one more time.
Smile


[Updated on: Fri, 21 November 2008 02:33] by Moderator

Report message to a moderator

Previous Topic: PLease give me answer of following questions?
Next Topic: ApEx Pros/Cons
Goto Forum:
  


Current Time: Fri Dec 09 15:31:28 CST 2016

Total time taken to generate the page: 0.45408 seconds