This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Aug 8, 2008 3:20 AM by 654150 RSS

Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0

451271 Newbie
Currently Being Moderated
I need help with procedure to get Anagrams from inserted word. Because I am new this can anyone please help me?

For example: I want to insert word "USB" and procedure will give me all the possible
combinations for this tree letters "U", "S" and "B".

Like this: "BSU", "BUS", "SUB", "SBU", ......

I have found some links with code but I can't find any in SQL language. Can anyone please help me or can anyone just give me some directions to some web site that could help me.

Just for help I have found this code's but I don't realy understand them:

Private Function Anagram(Word$) As String
Dim QQ%, An%, An1%
ReDim An2%(Len(Word))
Anagram = ""


For An = 1 To Len(Word)
NewRnd:
Randomize
An1 = Int(Rnd * Len(Word)) + 1


For QQ = 1 To An
If An2(QQ) = An1 Then GoTo NewRnd
Next QQ
An2(An) = An1
Anagram = Anagram + Mid(Word, An1, 1)
Next An
End Function

or this one:

import java.io.IOException;

public class AnagramApp {
static int size;

static int count;

static char[] charArray;

public static void main(String[] args) throws IOException {
String input = "Java Source and Support";
size = input.length();
count = 0;
charArray = new char[size];
for (int j = 0; j < size; j++)
charArray[j] = input.charAt(j);
doAnagram(size);
}

public static void doAnagram(int newSize) {
int limit;
if (newSize == 1) // if too small, return;
return;
// for each position,
for (int i = 0; i < newSize; i++) {
doAnagram(newSize - 1); // anagram remaining
if (newSize == 2) // if innermost,
display();
rotate(newSize); // rotate word
}
}

// rotate left all chars from position to end
public static void rotate(int newSize) {
int i;
int position = size - newSize;
// save first letter
char temp = charArray[position];
//shift others left
for (i = position + 1; i < size; i++)
charArray[i - 1] = charArray;
//put first on right
charArray[i - 1] = temp;
}

public static void display() {
System.out.print(++count + " ");
for (int i = 0; i < size; i++)
System.out.print(charArray[i]);
System.out.println();
}
}
  • 1. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    jonjac Explorer
    Currently Being Moderated
    Hello Jero,

    If you need help with reading VB or Java code, then I suggest that you go to a VB or Java forum.

    After you are familiar with the algorithm of creating anagrams (which consist, by the way, only of intelligible phrases), you might want to start by learning PL/SQL.

    Jonathan
  • 2. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    451271 Newbie
    Currently Being Moderated
    I was just giving a example witch I found on net but I want to do this in sql,
    can anyone please help me with my problem?

    Thanks
  • 3. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    MichaelS Guru
    Currently Being Moderated
    How about this?:
    DIESL10R>  DEFINE col=USB
    
    old: SELECT permutations
      FROM (SELECT     REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',') permutations
                  FROM (SELECT     SUBSTR ('&col', LEVEL, 1) n
                              FROM DUAL
                        CONNECT BY LEVEL <= LENGTH ('&col')) yourtable
            CONNECT BY NOCYCLE n != PRIOR n)
     WHERE LENGTH (permutations) = LENGTH ('&col')
    
    new: SELECT permutations
      FROM (SELECT     REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',') permutations
                  FROM (SELECT     SUBSTR ('USB', LEVEL, 1) n
                              FROM DUAL
                        CONNECT BY LEVEL <= LENGTH ('USB')) yourtable
            CONNECT BY NOCYCLE n != PRIOR n)
     WHERE LENGTH (permutations) = LENGTH ('USB')
    
    PERMUTATIONS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    USB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
    UBS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
    SUB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
    SBU                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
    BUS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
    BSU                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
    
    
    6 rows selected.
  • 4. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    94799 Explorer
    Currently Being Moderated
    How about this
    Nice, but NOCYCLE is 10g only.
  • 5. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    451271 Newbie
    Currently Being Moderated
    Thanks it doesn't work on 9i but it does work on 10 so thanks again for your help.

    Thank you
  • 6. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    451271 Newbie
    Currently Being Moderated
    Hello again,

    I have tried your procedure, but it work's only for words that are 3 characters long if you input words longer than 3 characters it doesn't work any more.

    I need this to work for longer words too? Can you tell me what to do?

    Thanks for your help
  • 7. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    MichaelS Guru
    Currently Being Moderated
    I need this to work for longer words too? Can you tell me what to do?
    hmm ...actually there is nothing more you need to do. Works perfectly for longer words too.

    What are you doing differently?:
    SQL>  SET define on
    
    SQL>  DEFINE col=USBC
    
    old: SELECT permutations
      FROM (SELECT     REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',') permutations
                  FROM (SELECT     SUBSTR ('&col', LEVEL, 1) n
                              FROM DUAL
                        CONNECT BY LEVEL <= LENGTH ('&col')) yourtable
            CONNECT BY NOCYCLE n != PRIOR n)
     WHERE LENGTH (permutations) = LENGTH ('&col')
    new: SELECT permutations
      FROM (SELECT     REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',') permutations
                  FROM (SELECT     SUBSTR ('USBC', LEVEL, 1) n
                              FROM DUAL
                        CONNECT BY LEVEL <= LENGTH ('USBC')) yourtable
            CONNECT BY NOCYCLE n != PRIOR n)
     WHERE LENGTH (permutations) = LENGTH ('USBC')
    
    PERMUTATIONS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    USBC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    USCB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    UBSC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    UBCS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    UCSB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    UCBS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    SUBC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    SUCB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    SBUC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    SBCU                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    SCUB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    SCBU                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    BUSC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    BUCS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    BSUC                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    BSCU                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    BCUS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    BCSU                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    CUSB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    CUBS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    CSUB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    CSBU                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    CBUS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    CBSU                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    
    
    24 rows selected.
  • 8. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    94799 Explorer
    Currently Being Moderated
    but it work's only for words that are 3 characters long
    erm... yes it does. How about you just tell us what the problem is, not just 'it doesn't work'.
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

    SQL> VARIABLE word VARCHAR2 (10);
    SQL> EXEC :word := 'WHAT';

    PL/SQL procedure successfully completed.

    SQL> SELECT permutations
      2  FROM  (SELECT REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',') permutations
      3         FROM  (SELECT  SUBSTR (:word, LEVEL, 1) n
      4                FROM    DUAL
      5                CONNECT BY LEVEL <= LENGTH (:word)) yourtable
      6         CONNECT BY NOCYCLE n != PRIOR n)
      7  WHERE  LENGTH (permutations) = LENGTH (:word);

    PERMUTATIONS
    --------------------------------------------------------------------------------
    WHAT
    WHTA
    WAHT
    WATH
    WTHA
    WTAH
    HWAT
    HWTA
    HAWT
    HATW
    HTWA
    HTAW
    AWHT
    AWTH
    AHWT
    AHTW
    ATWH
    ATHW
    TWHA
    TWAH
    THWA
    THAW
    TAWH
    TAHW

    24 rows selected.

    SQL>
  • 9. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    451271 Newbie
    Currently Being Moderated
    I have tried your word and it works fine, but when I try this one I get "no rows selected"

    SQL> SET define on
    SQL> DEFINE col=JERNEJ

    SELECT permutations
    FROM (SELECT REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',') permutations
    FROM (SELECT SUBSTR ('&col', LEVEL, 1) n
    FROM DUAL
    CONNECT BY LEVEL <= LENGTH ('&col')) yourtable
    CONNECT BY NOCYCLE n != PRIOR n)
    WHERE LENGTH (permutations) = LENGTH ('&col');


    old 3: FROM (SELECT SUBSTR ('&col', LEVEL, 1) n
    new 3: FROM (SELECT SUBSTR ('JERNEJ', LEVEL, 1) n
    old 5: CONNECT BY LEVEL <= LENGTH ('&col')) yourtable
    new 5: CONNECT BY LEVEL <= LENGTH ('JERNEJ')) yourtable
    old 7: WHERE LENGTH (permutations) = LENGTH ('&col')
    new 7: WHERE LENGTH (permutations) = LENGTH ('JERNEJ')

    no rows selected

    SQL>


    Why is that?
  • 10. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    451271 Newbie
    Currently Being Moderated
    I have found out why.
    If you insert same letters it doesn't work.

    Can you fix that or is this impossible?
  • 11. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    MichaelS Guru
    Currently Being Moderated
    If you insert same letters it doesn't work.
    You are right. Therefore a small modification:
    SQL>  SET define on
    SQL>  DEFINE col=AABB
    old: SELECT DISTINCT permutations
               FROM (SELECT     REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',')
                                                                     permutations
                           FROM (SELECT     LEVEL l, SUBSTR ('&col', LEVEL, 1) n
                                       FROM DUAL
                                 CONNECT BY LEVEL <= LENGTH ('&col')) yourtable
                     CONNECT BY NOCYCLE l != PRIOR l)
              WHERE LENGTH (permutations) = LENGTH ('&col')
    new: SELECT DISTINCT permutations
               FROM (SELECT     REPLACE (SYS_CONNECT_BY_PATH (n, ','), ',')
                                                                     permutations
                           FROM (SELECT     LEVEL l, SUBSTR ('AABB', LEVEL, 1) n
                                       FROM DUAL
                                 CONNECT BY LEVEL <= LENGTH ('AABB')) yourtable
                     CONNECT BY NOCYCLE l != PRIOR l)
              WHERE LENGTH (permutations) = LENGTH ('AABB')
    
    PERMUTATIONS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ABBA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    BAAB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    AABB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    ABAB                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    BABA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    BBAA                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
    
    
    6 rows selected.
  • 12. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    451271 Newbie
    Currently Being Moderated
    Thank you wary much it is working fine now.

    Thanks again
  • 13. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    PL/SQL seems more appropriate than hierarchy

    You can start with something like
    SQL> create or replace type table_of_varchar2 as table of varchar2(255);
      2  /
    
    Type created.
    
    SQL> 
    SQL> create or replace package anagram is
      2      procedure p(x varchar2, y varchar2 default null);
      3      function f(x varchar2) return table_of_varchar2;
      4  end;
      5  /
    
    Package created.
    
    SQL> 
    SQL> create or replace package body anagram is
      2      t table_of_varchar2;
      3  
      4      procedure p(x varchar2, y varchar2 default null) is
      5      begin
      6          if (length(x) = 1) then
      7              t.extend(1);
      8              t(t.last):=y||x;
      9          else
     10              for i in 1..length(x) loop
     11                  p(substr(x,1,i-1)||substr(x,i+1),y||substr(x,i,1));
     12              end loop;
     13          end if;
     14      end p;
     15  
     16      function f(x varchar2) return table_of_varchar2 is
     17      begin
     18          t := new table_of_varchar2();
     19          p(x);
     20          return t;
     21      end f;
     22  
     23  end;
     24  /
    
    Package body created.
    
    SQL> 
    SQL> set pages 999;
    SQL> 
    SQL> select * from table(anagram.f('ABC'));
    
    COLUMN_VALUE
    --------------------------------------------------------------------------------
    ABC
    ACB
    BAC
    BCA
    CAB
    CBA
    
    6 rows selected.
    
    SQL> 
    SQL> select distinct * from table(anagram.f('ABAB'));
    
    COLUMN_VALUE
    --------------------------------------------------------------------------------
    ABBA
    BAAB
    BABA
    BBAA
    ABAB
    AABB
    
    6 rows selected.
  • 14. Re: Anagram Help - Oracle9i Enterprise Edition Release 9.2.0.7.0
    MichaelS Guru
    Currently Being Moderated
    PL/SQL seems more appropriate than hierarchy
    Laurent, why do you think so?
1 2 Previous Next