SQL & PL/SQL

Suman,

Only if the length of your string is constant, I know a solution:

In SQL:

SQL> with t as
  2  ( select 'HER' string from dual
  3  )
  4  select substr(string,a.n,1) ||
  5         substr(string,b.n,1) ||
  6         substr(string,c.n,1)
  7    from (select level n from dual connect by level <= 3) a
  8       , (select level n from dual connect by level <= 3) b
  9       , (select level n from dual connect by level <= 3) c
 10       , t
 11   where a.n != b.n
 12     and b.n != c.n
 13     and c.n != a.n
 14  /

SUB
---
REH
ERH
RHE
HRE
EHR
HER

6 rijen zijn geselecteerd.

and in PL/SQL:

SQL> declare
  2    cn_string constant varchar2(3) := 'HER';
  3  begin
  4    for i in 1..3
  5    loop
  6      for j in 1..3
  7      loop
  8        for k in 1..3
  9        loop
 10          if    i != j
 11            and j != k
 12            and k != i
 13          then
 14            dbms_output.put_line
 15            ( substr(cn_string,i,1) ||
 16              substr(cn_string,j,1) ||
 17              substr(cn_string,k,1)
 18            );
 19          end if;
 20        end loop;
 21      end loop;
 22    end loop;
 23  end;
 24  /
HER
HRE
EHR
ERH
RHE
REH

But a solution for all lengths would be a real challenge. I am curious if someone is able to present a dynamical solution.

Regards,
Rob.

How about...

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> VARIABLE word VARCHAR2 (3);
SQL> EXEC :word := 'HER';

PL/SQL procedure successfully completed.

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

WORD
----------------------------------------------------------------------
HER
HRE
EHR
ERH
RHE
REH

6 rows selected.

SQL> 

Nice work padders.

Very impressive !

Good solution.

for the string 'hello' nothing is displaying but for 'oracle' its displaying.

Ooops, duplicate letter broke it :-(

Try...

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> VARIABLE word VARCHAR2 (5);
SQL> EXEC :word := 'HELLO';

PL/SQL procedure successfully completed.

SQL> SET PAGES 1000;
SQL> SELECT REPLACE (SYS_CONNECT_BY_PATH (letter, '/'), '/') word 
  2  FROM  (SELECT LEVEL lvl, SUBSTR (:word, LEVEL, 1) letter 
  3         FROM   dual 
  4         CONNECT BY LEVEL <= LENGTH (:word)) 
  5  WHERE  LEVEL = LENGTH (:word)
  6  CONNECT BY NOCYCLE lvl != PRIOR lvl;

WORD
--------------------------------------------------------------------------------
HELLO
HELOL
HELLO
HELOL
HEOLL
HEOLL
HLELO
HLEOL
HLLEO
HLLOE
HLOEL
HLOLE
HLELO
HLEOL
HLLEO
HLLOE
HLOEL
HLOLE
HOELL
HOELL
HOLEL
HOLLE
HOLEL
HOLLE
EHLLO
EHLOL
EHLLO
EHLOL
EHOLL
EHOLL
ELHLO
ELHOL
ELLHO
ELLOH
ELOHL
ELOLH
ELHLO
ELHOL
ELLHO
ELLOH
ELOHL
ELOLH
EOHLL
EOHLL
EOLHL
EOLLH
EOLHL
EOLLH
LHELO
LHEOL
LHLEO
LHLOE
LHOEL
LHOLE
LEHLO
LEHOL
LELHO
LELOH
LEOHL
LEOLH
LLHEO
LLHOE
LLEHO
LLEOH
LLOHE
LLOEH
LOHEL
LOHLE
LOEHL
LOELH
LOLHE
LOLEH
LHELO
LHEOL
LHLEO
LHLOE
LHOEL
LHOLE
LEHLO
LEHOL
LELHO
LELOH
LEOHL
LEOLH
LLHEO
LLHOE
LLEHO
LLEOH
LLOHE
LLOEH
LOHEL
LOHLE
LOEHL
LOELH
LOLHE
LOLEH
OHELL
OHELL
OHLEL
OHLLE
OHLEL
OHLLE
OEHLL
OEHLL
OELHL
OELLH
OELHL
OELLH
OLHEL
OLHLE
OLEHL
OLELH
OLLHE
OLLEH
OLHEL
OLHLE
OLEHL
OLELH
OLLHE
OLLEH

120 rows selected.

SQL> 

The query probably needs a distinct as well, because it now contains duplicates.

Using 10g and my string generator from second part of 432647, a solution could look like this:

SELECT data 
  FROM TABLE(regex_utils.gen_data('HER', 3))
 WHERE REGEXP_LIKE(data, '(.){3}')
   AND NOT REGEXP_LIKE(data, '(.).*\1')
  ;

Another solution would could be changing the code of that string generator.

C.

Hi CD, BTW, waiting for the regular expression part 3....
Thank you.

Hi all,

Thanks for your response. But I use oracle 9i and not familiar with the REG_EXP of 10G.

So, is it possible to do it with this. For that matter, not only in SQL,PLSQL but generally i am confused with the logic of implementing this.

If somebody could help me with the logic behind, then it would be of great help.

For 3 lettered and 4 lettered I managed using swapping each time ( keeping a letter constant each time), but how to generalize it?

Please help

Hi, This post is great... I would like to apply this in a slightly different manour... I have a table that contains string values at different locations that they can exist in a string... ie.

id level value
1 1 A
2 1 B
3 1 C
4 2 0
5 2 1
6 2 2
7 3 X
8 3 Y
9 3 Z

{A-C}{0-3}{X-Z}

and there can be multiple levels, with multiple potential values at each level... in the example above there will be 3x3x3 potential unique combinations...

would could i use your logic (or some similar logic) to return all potential combinations of the data in my table

Hi, You have your own question, why not start your own thread? It could turn out to be just as great!

Whenever you have a question, it helps to post:
(1) The version of Oracle (and any other relevant software) you're using. It sounds like this will be especially important for this problem.
(2) A little sample data. Executable SQL statements (like "CREATE TABLE AS ..." or "INSERT ..." statements) are best.
(3) The results you want from that data
(4) Your best attempt so far (formatted)

Formatted tabular output is okay for (3). Type these 6 characters:

{code}

(small letters only, inside curly brackets) before and after sections of formatted text, to preserve spacing.

Thanks Frank, I have opened a new question for this at : 977772

Any idea on how you would do this if you didn't know the strings? or their lengths?