Forum Stats

  • 3,733,254 Users
  • 2,246,738 Discussions
  • 7,856,636 Comments

Discussions

Does anyone have some wizzy code or bright ideas?

563457
563457 Member Posts: 27
edited October 2007 in SQL & PL/SQL
I have a string variable - varchar2(240) maximum - which is arbitarily filled with 3 letter codes. Any one or more of these codes may be duplicated within the string. What I need to do is to strip out all the duplications and end up with a single contiguous string containing all the unique code options. e.g.

Initial string 'AAABBBCCCBBBDDDAAAEEEBBB'

should end up as :-

'AAABBBCCCDDDEEE'

Hope this makes sense - also there is no need to place the unique codes in any kind of order.

Thanks for your help...

Comments

  • 94799
    94799 Member Posts: 2,208
    SQL or PL/SQL, version of Oracle?
  • 563457
    563457 Member Posts: 27
    sorry - PL/SQL
  • riedelme
    riedelme Member Posts: 3,528
    edited September 2007
    Unless somebody has such a routine you'll have to write it yourself. The general logic for one way would be

    parse string into associative array indexed by 3-letter code
    loop through associative array using first, next, and last methods to recreate string

    Message was edited by:
    riedelme
  • 94799
    94799 Member Posts: 2,208
    parse string into associative array indexed by 3-letter code
    Nice. The other way that springs to mind is to parse into nested table then use SET collection method to eliminate duplicates.
  • riedelme
    riedelme Member Posts: 3,528
    < parse into nested table then use SET collection method to eliminate duplicates >
    Better, since looping should be easier
  • 229023
    229023 Member Posts: 2,305
    This may be a lengthier way, but it yields results(assuming you only have 3 same letter contiguous characters in your string)
    [email protected]> WITH mytab AS
    2 (SELECT 'AAABBBCCCBBBDDDAAAEEEBBB' mycol FROM DUAL)
    3 SELECT REVERSE(REPLACE(MAX(SYS_CONNECT_BY_PATH(mystr,'/')),'/','')) output_str
    4 FROM
    5 (
    6 SELECT mystr, rownum rn FROM
    7 (
    8 SELECT distinct substr(mycol, ((level-1) * 3) + 1,3) mystr FROM mytab
    9 CONNECT BY LEVEL <= LENGTH(mycol) / 3
    10 )
    11 )
    12 connect by rn + 1 = prior rn
    13 ;

    OUTPUT_STR

    AAABBBCCCDDDEEE
  • cd_2
    cd_2 Member Posts: 5,021
    And in 10g, regular expressions and MODEL clause can do the job:
    WITH t AS (SELECT 'AAABBBCCCBBBDDDAAAEEEBBB' col1
                 FROM dual
              )
    SELECT c1
         , c2
      FROM t
      MODEL 
       DIMENSION BY (0 dim)
       MEASURES(col1 c1, CAST ('' AS VARCHAR2(255)) c2, CAST ('x' AS VARCHAR2(255)) c3)
       RULES ITERATE(99) UNTIL (c3[0] IS NULL)
       (c3[0] = REGEXP_SUBSTR(c1[0], '([A-Z])\1{2}', 1, ITERATION_NUMBER+1)
       ,c2[0] = c2[0] || CASE WHEN NVL(INSTR(c2[0], c3[0]), 0) = 0 THEN c3[0] END
       ) 
    ;         
    
    C1                             C2
    ------------------------------ ------------------------------
    AAABBBCCCBBBDDDAAAEEEBBB       AAABBBCCCDDDEEE      
    C.
  • 572471
    572471 Member Posts: 984 Green Ribbon
    And in 10g, regular expressions and MODEL clause can
    do the job:
    another way to do it with MODEL (less iterations, less measures):
    SQL> WITH t AS (SELECT 'AAABBBCCCBBBDDDAAAEEEBBB' str
      2               FROM dual
      3            )
      4            select str from t
      5             model
      6              dimension by (0 dim)
      7              measures(str)
      8              rules iterate(100) until (str[0] = previous(str[0]))
      9            (str[0]=regexp_replace(str[0],'(([[:alpha:]])\2{2})(.*?)\1','\1\3'));
    
    STR
    ------------------------
    AAABBBCCCDDDEEE
    
    SQL>
  • cd_2
    cd_2 Member Posts: 5,021
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    before
    AAABBBCCCBBBDDDAAAEEEBBB
    after
    AAABBBCCCDDDEEE

    If you can use Editor,
    many times
    replace
    ((.)(?=(\2+))\3((?!\2).)+)\2+
    to
    \1
    If you can use JavaStoredProcedure,
    many times
    replace
    ((.)\2++((?!\2).)+)\2+
    to
    \1
    Possessive quantifiers
    http://java.sun.com/j2se/1.4.2/docs/api/java/util/regex/Pattern.html
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    oops

    I took mistake.
    We do not need Possessive quantifiers.

    many times
    replace
    ((.)\2+((?!\2).)+)\2+
    to
    \1
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited October 2007
    sorry
    I took more mistake.

    before
    AAABBBCCCBBBDDDAAAEEEBBB
    after
    AAABBBCCCDDDEEE

    before
    AAABBBCCCCCCDDDDDDEEEDDDEEEDDD
    after
    AAABBBCCCDDDEEE

    many times
    replace
    ((.)\2{2}(.*?))\2+
    to
    \1
  • 563457
    563457 Member Posts: 27
    Many thanks for everyones help with this... not only do I now have a slick solution - but it looks like I know what I'm doing!!

    Greatly appreciated.
This discussion has been closed.