6 Replies Latest reply: Jan 18, 2013 8:57 AM by Frank Kulash RSS

    return all possible pattern from a character string

    982031
      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.
          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
            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
              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
                Thanks Frank it's nice!
                • 6. Re: return all possible pattern from a character string
                  Frank Kulash
                  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).