This discussion is archived
6 Replies Latest reply: Jan 18, 2013 6:57 AM by Frank Kulash RSS

return all possible pattern from a character string

982031 Newbie
Currently Being Moderated
Hi all,

I would like to return all possible patterns from a given string, e.g when the string is 'ABC' I should get

ABC
ACB
BAC
BCA
CAB
CBA

as well as

AAA
BBB
CCC
ABB
ACC
BAA
BCC
CAA
CBB


I have been able to write this:

select permut
from (select replace (SYS_CONNECT_BY_PATH (e, ','), ',') permut
from (select SUBSTR('ABC', level, 1) e
from dual
connect by level <= length('ABC'))
connect by nocycle e != prior e)
where length(permut) = 3


but the output is only:

ABC
ACB
BAC
BCA
CAB
CBA


any ideas? for ora10g or 11g sql/plsql

Thanks
  • 1. Re: return all possible pattern from a character string
    Sven W. Guru
    Currently Being Moderated
    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
  • 2. Re: return all possible pattern from a character string
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    This works with a string of any length:
    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 assumes you know of some sub-string (such a ',' in the example above) that never occurs in the string.
  • 3. Re: return all possible pattern from a character string
    Stew Ashton Expert
    Currently Being Moderated
    This is similar to Frank's solution but a little simpler:
    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);
    [Edit: I guess the main difference is I don't see the need for DISTINCT.]

    Edited by: Stew Ashton on Jan 18, 2013 1:58 PM
  • 4. Re: return all possible pattern from a character string
    982031 Newbie
    Currently Being Moderated
    Thanks Frank it's nice!
  • 5. Re: return all possible pattern from a character string
    982031 Newbie
    Currently Being Moderated
    Thanks Ashton
  • 6. Re: return all possible pattern from a character string
    Frank Kulash Guru
    Currently Being Moderated
    Hi
    Stew Ashton wrote:
    ... I guess the main difference is I don't see the need for DISTINCT.
    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).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points