This content has been marked as final. Show 6 replies
Is your string always only 3 letters long? Then this would be possible:
with token as (select SUBSTR('ABC', level, 1) e from dual connect by level <= length('ABC')) select t1.e, t2.e, t3.e from token t1 cross join token t2 cross join token t3; e e_1 e_2 ----------------------- A A A A A B A A C A B A A B B A B C A C A A C B A C C B A A B A B B A C B B A B B B B B C B C A B C B B C C C A A C A B C A C C B A C B B C B C C C A C C B C C C
Hi,1 person found this helpful
Welcome to the forum!
This works with a string of any length:
This assumes you know of some sub-string (such a ',' in the example above) that never occurs in the string.
WITH got_letters AS ( SELECT LEVEL AS pos , SUBSTR (str, LEVEL, 1) AS letter , LENGTH (str) AS length_str FROM ( SELECT 'ABC' AS str FROM dual ) CONNECT BY LEVEL <= LENGTH (str) ) SELECT DISTINCT REPLACE ( SYS_CONNECT_BY_PATH (letter, ',') , ',' ) AS new_str FROM got_letters WHERE LEVEL = length_str CONNECT BY NOCYCLE LEVEL <= length_str ORDER BY new_str ;
This is similar to Frank's solution but a little simpler:
[Edit: I guess the main difference is I don't see the need for DISTINCT.]
with indata as ( select 'ABC' txt from dual ), chrs AS ( select substr(txt,level,1) chr FROM indata CONNECT BY level <= LENGTH(txt) ) select replace(sys_connect_by_path(chr, '/'), '/', '') permut from chrs where connect_by_isleaf = 1 connect by level <= (select length(txt) from indata);
Edited by: Stew Ashton on Jan 18, 2013 1:58 PM
Thanks Frank it's nice!
Stew Ashton wrote:It matters only if the letters in the original string are not unique. Without SELECT DISTINCT, a 3-character string always produces 27 rows of output, even if the string is 'ABA' (and there are only 8 distinct output rows) or 'AAA' (only 1 distinct output row).
... I guess the main difference is I don't see the need for DISTINCT.