Forum Stats

  • 3,734,036 Users
  • 2,246,863 Discussions
  • 7,857,008 Comments

Discussions

Tedious Query

Maestro_Vineet
Maestro_Vineet Member Posts: 135 Bronze Badge
edited January 2008 in SQL & PL/SQL
Hi
Experts ....here is something for u ppl, which appeared quite tedious for me lets see whether u have got the solution or not.

Suppose I have a table Dump with the following Construct and Value

#Dump
-------
Dcode Varchar2(15);

Dcode
.......
AAABBCCDDEEA
BBDDDFFAACC
DHSGHDADSFDF

NOW i need an output of somewhat like this
Dcode
......
ABCDE
BDFAC
DHSGAF

If its possible to get the desired output in a Single Select statement then i wud say 'Eureka' nor u can help me either with writing Procedure or Function.

Ur help is highly appreciated.

Regards
Vineet

Comments

  • 32685
    32685 Member Posts: 2,924
    Hello

    I think you can do this using SYS_CONNECT_BY_PATH but for some reason my installations of Oracle (9.2.0.4 and 9.2.0.6) don't like it and return from the query with end of file on communication channel. I also tried using the string_agg function (both can be found at http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php)

    Anyway, here's what I have so far - I'm making the assumption that you need to preserve the order of characters as they appeared in the string, and that is why the pipelined function takes in a cursor. I tried selecting from the dt_test_dump table and using the dcode column as the only input to the function, but this seemed to cause problems with the number of calls to the function and the results fluctuated with different orderings.
    CREATE OR REPLACE TYPE ot_CharAndPos IS OBJECT
    ( dcode varchar2(15),
    character VARCHAR2(1),
    pos NUMBER
    )
    /
    CREATE OR REPLACE TYPE tt_Chars AS TABLE OF ot_CharAndPos;
    /
    CREATE OR REPLACE FUNCTION f_DumpCharsToRows(ac_DumpChars IN sys_refcursor)
    RETURN tt_Chars
    PIPELINED
    IS

    ln_Length NUMBER;
    lv_Dcode VARCHAR2(15);

    BEGIN

    LOOP

    FETCH ac_DumpChars INTO lv_Dcode;

    EXIT WHEN ac_DumpChars%NOTFOUND;

    ln_Length := LENGTH(lv_Dcode);

    FOR li_CharPos IN 1..ln_Length-1 LOOP

    PIPE ROW(ot_CharAndPos( lv_Dcode,
    SUBSTR(lv_Dcode, li_CharPos, 1),
    li_CharPos
    )
    );

    END LOOP;

    END LOOP;

    RETURN;

    END;
    /
    Test data:
    CREATE TABLE dt_test_dump (dcode varchar2(15))
    /
    INSERT INTO dt_test_dump
    VALUES( 'AAABBCCDDEEA')
    /
    INSERT INTO dt_test_dump
    VALUES( 'DHSGHDADSFDF')
    /
    INSERT INTO dt_test_dump
    VALUES( 'BBDDDFFAACC')
    /
    Inner query:
    SQL> SELECT
    2 a.dcode,
    3 a.character,
    4 a.pos,
    5 MIN(a.pos) OVER (PARTITION BY a.dcode,a.character ORDER BY a.pos) min_pos
    6 FROM
    7 TABLE(f_DumpCharsToRows(CURSOR(SELECT dcode FROM dt_test_dump))) a
    8 /

    DCODE C POS MIN_POS
    --------------- - ---------- ----------
    AAABBCCDDEEA A 1 1
    AAABBCCDDEEA A 2 1
    AAABBCCDDEEA A 3 1
    AAABBCCDDEEA B 4 4
    AAABBCCDDEEA B 5 4
    AAABBCCDDEEA C 6 6
    AAABBCCDDEEA C 7 6
    AAABBCCDDEEA D 8 8
    AAABBCCDDEEA D 9 8
    AAABBCCDDEEA E 10 10
    AAABBCCDDEEA E 11 10
    BBDDDFFAACC A 8 8
    BBDDDFFAACC A 9 8
    BBDDDFFAACC B 1 1
    BBDDDFFAACC B 2 1
    BBDDDFFAACC C 10 10
    BBDDDFFAACC D 3 3
    BBDDDFFAACC D 4 3
    BBDDDFFAACC D 5 3
    BBDDDFFAACC F 6 6
    BBDDDFFAACC F 7 6
    DHSGHDADSFDF A 7 7
    DHSGHDADSFDF D 1 1
    DHSGHDADSFDF D 6 1
    DHSGHDADSFDF D 8 1
    DHSGHDADSFDF D 11 1
    DHSGHDADSFDF F 10 10
    DHSGHDADSFDF G 4 4
    DHSGHDADSFDF H 2 2
    DHSGHDADSFDF H 5 2
    DHSGHDADSFDF S 3 3
    DHSGHDADSFDF S 9 3

    32 rows selected.
    Query to get only the first appearance of each character:
    SQL> SELECT
    2 dcode,
    3 character,
    4 pos
    5 FROM
    6 (
    7 SELECT
    8 a.dcode,
    9 a.character,
    10 a.pos,
    11 MIN(a.pos) OVER (PARTITION BY a.dcode,a.character ORDER BY a.pos) min_pos
    12 FROM
    13 TABLE(f_DumpCharsToRows(CURSOR(SELECT dcode FROM dt_test_dump))) a
    14 )
    15 WHERE
    16 pos = min_pos
    17 ORDER BY
    18 dcode,
    19 pos
    20 /

    DCODE C POS
    --------------- - ----------
    AAABBCCDDEEA A 1
    AAABBCCDDEEA B 4
    AAABBCCDDEEA C 6
    AAABBCCDDEEA D 8
    AAABBCCDDEEA E 10
    BBDDDFFAACC B 1
    BBDDDFFAACC D 3
    BBDDDFFAACC F 6
    BBDDDFFAACC A 8
    BBDDDFFAACC C 10
    DHSGHDADSFDF D 1
    DHSGHDADSFDF H 2
    DHSGHDADSFDF S 3
    DHSGHDADSFDF G 4
    DHSGHDADSFDF A 7
    DHSGHDADSFDF F 10

    16 rows selected.
    Here's the result from the string_agg function, which you can see doesn't preserve the order of the string (I assume it's down to the merge but I haven't had a propper look at it yet).
    SQL> SELECT
    2 dcode,
    3 REPLACE(string_agg(character),',') dcode_no_repeats
    4 FROM
    5 ( SELECT
    6 dcode,
    7 character,
    8 pos
    9 FROM
    10 (
    11 SELECT
    12 a.dcode,
    13 a.character,
    14 a.pos,
    15 MIN(a.pos) OVER (PARTITION BY a.dcode,a.character ORDER BY a.pos) min_pos
    16 FROM
    17 TABLE(f_DumpCharsToRows(CURSOR(SELECT dcode FROM dt_test_dump))) a
    18 )
    19 WHERE
    20 pos = min_pos
    21 ORDER BY
    22 dcode,
    23 pos
    24 )
    25 GROUP BY
    26 dcode
    27 /

    AAABBCCDDEEA ABCED
    BBDDDFFAACC BDFCA
    DHSGHDADSFDF DAFGHS
    But I'm sure if you try the SYS_CONNECT_BY_PATH technique, you'll get the result you're looking for. There are plenty of other ways to do this using procedures etc and I'm sure there's probably a way to do this that doesn't involve any pl/sql at all.

    Anway, this is my attempt and hopefully it will help a bit :-)

    HTH

    David
  • 451529
    451529 Member Posts: 196
    HI

    i think single select statement is not enough for that ..... use this function

    create or replace function unik(str1 varchar2) return varchar2
    is
    res varchar2(20);
    part varchar2(1);
    BEgin
    for i in 1.. length(str1)
    loop
    part:=substr(str1,i,1);
    if instr(res,part)>0 then
    null;
    else
    res:=res|| part;
    end if;
    end loop;
    RETURN res;
    end;


    Ashish
  • 121011
    121011 Member Posts: 566
    SQL> select * from dump;

    DUMP#
    ------------------------------
    AAABBCCDDEEA
    BBDDDFFAACC
    DHSGHDADSFDF

    SQL> select drn, dl, min(crn) mcrn
      2  from (
      3  select d.rn drn, c.rn crn, substr(dump#, c.rn,1) dl
      4  from
      5  (select rownum rn, dump#
      6  from dump) d,
      7  (select rownum rn
      8  from cat) c
    &nbsp;&nbsp;9&nbsp;&nbsp;where&nbsp;c.rn&nbsp;<=&nbsp;length(d.dump#)
    &nbsp;10&nbsp;&nbsp;)
    &nbsp;11&nbsp;&nbsp;group&nbsp;by&nbsp;drn,&nbsp;dl
    &nbsp;12&nbsp;&nbsp;order&nbsp;by&nbsp;1,&nbsp;3;

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DRN&nbsp;D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MCRN
    ---------&nbsp;-&nbsp;---------
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;E&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;F&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;H&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;S&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;G&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;F&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10

    16&nbsp;ñòðîê&nbsp;âûáðàíî.

    SQL>&nbsp;

    Use stragg() to complite this query (Oracle vertion >= 9).
  • 121011
    121011 Member Posts: 566
    Stupid method:

    SQL>&nbsp;select&nbsp;substr(dump#,&nbsp;1,&nbsp;7)||replace(substr(dump#,&nbsp;8),&nbsp;substr(dump#,&nbsp;7,1),&nbsp;NULL)&nbsp;dump#
    &nbsp;&nbsp;2&nbsp;&nbsp;from
    &nbsp;&nbsp;3&nbsp;&nbsp;(select&nbsp;substr(dump#,&nbsp;1,&nbsp;6)||replace(substr(dump#,&nbsp;7),&nbsp;substr(dump#,&nbsp;6,1),&nbsp;NULL)&nbsp;dump#
    &nbsp;&nbsp;4&nbsp;&nbsp;from
    &nbsp;&nbsp;5&nbsp;&nbsp;(select&nbsp;substr(dump#,&nbsp;1,&nbsp;5)||replace(substr(dump#,&nbsp;6),&nbsp;substr(dump#,&nbsp;5,1),&nbsp;NULL)&nbsp;dump#
    &nbsp;&nbsp;6&nbsp;&nbsp;from
    &nbsp;&nbsp;7&nbsp;&nbsp;(select&nbsp;substr(dump#,&nbsp;1,&nbsp;4)||replace(substr(dump#,&nbsp;5),&nbsp;substr(dump#,&nbsp;4,1),&nbsp;NULL)&nbsp;dump#
    &nbsp;&nbsp;8&nbsp;&nbsp;from
    &nbsp;&nbsp;9&nbsp;&nbsp;(select&nbsp;substr(dump#,&nbsp;1,&nbsp;3)||replace(substr(dump#,&nbsp;4),&nbsp;substr(dump#,&nbsp;3,1),&nbsp;NULL)&nbsp;dump#
    &nbsp;10&nbsp;&nbsp;from
    &nbsp;11&nbsp;&nbsp;(select&nbsp;substr(dump#,&nbsp;1,&nbsp;2)||replace(substr(dump#,&nbsp;3),&nbsp;substr(dump#,&nbsp;2,1),&nbsp;NULL)&nbsp;dump#
    &nbsp;12&nbsp;&nbsp;from
    &nbsp;13&nbsp;&nbsp;(select&nbsp;substr(dump#,&nbsp;1,&nbsp;1)||replace(substr(dump#,&nbsp;2),&nbsp;substr(dump#,&nbsp;1,1),&nbsp;NULL)&nbsp;dump#
    &nbsp;14&nbsp;&nbsp;from&nbsp;dump
    &nbsp;15&nbsp;&nbsp;)
    &nbsp;16&nbsp;&nbsp;)
    &nbsp;17&nbsp;&nbsp;)
    &nbsp;18&nbsp;&nbsp;)
    &nbsp;19&nbsp;&nbsp;)
    &nbsp;20&nbsp;&nbsp;);

    DUMP#
    ------------------------------
    ABCDE
    BDFAC
    DHSGAF

    SQL>&nbsp;

    (:)))
  • cd_2
    cd_2 Member Posts: 5,021
    If the result can be in alphabetical sort order, you can try this:
    WITH T AS (SELECT 'AAABBCCDDEEA' dmp
    FROM dual
    UNION
    SELECT 'BBDDDFFAACC' dmp
    FROM dual
    UNION
    SELECT 'DHSGHDADSFDF' dmp
    FROM dual
    )
    SELECT REPLACE(TRANSLATE('ABCDEFGHIJKLMNOPQRSTUVWXYZ', TRANSLATE('ABCDEFGHIJKLMNOPQRSTUVWXYZ', t.dmp, ' ') , ' '), ' ', '')
    FROM t;
    C.
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    create table t as select 
    'AAABBCCDDEEA' x from dual union select
    'BBDDDFFAACC' from dual union select
    'DHSGHDADSFDF' from dual;
    
    select x,
    decode(instr(x,'A'),0,null,'A')||
    decode(instr(x,'B'),0,null,'B')||
    decode(instr(x,'C'),0,null,'C')||
    decode(instr(x,'D'),0,null,'D')||
    decode(instr(x,'E'),0,null,'E')||
    decode(instr(x,'F'),0,null,'F')||
    decode(instr(x,'G'),0,null,'G')||
    decode(instr(x,'H'),0,null,'H')||
    decode(instr(x,'I'),0,null,'I')||
    decode(instr(x,'J'),0,null,'J')||
    decode(instr(x,'K'),0,null,'K')||
    decode(instr(x,'L'),0,null,'L')||
    decode(instr(x,'M'),0,null,'M')||
    decode(instr(x,'N'),0,null,'N')||
    decode(instr(x,'O'),0,null,'O')||
    decode(instr(x,'P'),0,null,'P')||
    decode(instr(x,'Q'),0,null,'Q')||
    decode(instr(x,'S'),0,null,'S')||
    decode(instr(x,'T'),0,null,'T')||
    decode(instr(x,'U'),0,null,'U')||
    decode(instr(x,'V'),0,null,'V')||
    decode(instr(x,'W'),0,null,'W')||
    decode(instr(x,'X'),0,null,'X')||
    decode(instr(x,'Y'),0,null,'Y')||
    decode(instr(x,'Z'),0,null,'Z') y
    from t;
    
    X                Y
    ---------------- -------------------------
    AAABBCCDDEEA     ABCDE
    BBDDDFFAACC      ABCDF
    DHSGHDADSFDF     ADFGHS
  • 440764
    440764 Member Posts: 60
    Hi Laurent,

    Ur query is really great!! But I think his requirement is slightly different.

    for example, if BBDDDFFAACC is the input string then the expected output is BDFAC not ABCDF.

    is there any way to maintain the order?

    Thanks.
    James
  • 32685
    32685 Member Posts: 2,924
    Hello

    To maintain the order of the characters using the string_agg function, you use it like so:
    SQL> SELECT DISTINCT
    2 dcode,
    3 REPLACE(string_agg(character) OVER (PARTITION BY dcode ORDER BY dcode),',') dcode_no_repeats
    4 FROM
    5 ( SELECT
    6 dcode,
    7 character,
    8 pos
    9 FROM
    10 (
    11 SELECT
    12 a.dcode,
    13 a.character,
    14 a.pos,
    15 MIN(a.pos) OVER (PARTITION BY a.dcode,a.character ORDER BY a.pos) min_pos
    16 FROM
    17 TABLE(f_DumpCharsToRows(CURSOR(SELECT dcode FROM dt_test_dump))) a
    18 )
    19 WHERE
    20 pos = min_pos
    21 ORDER BY
    22 dcode,
    23 pos
    24 )
    25 /

    DCODE DCODE_NO_REPEATS
    --------------- --------------------
    AAABBCCDDEEA ABCDE
    BBDDDFFAACC BDFAC
    DHSGHDADSFDF DHSGAF
    Only just figured that one out so I'm not quite sure of the mechanics yet. By combining this with one of the queries that extract the list of chars without using a pipelined function (bit of a red face on my part! :-)) you should be able to preserve the order of the string and have an efficient query, all with very little coding.

    HTH

    David
  • APC
    APC Member Posts: 11,316 Bronze Crown
    Use stragg() to complite this query (Oracle vertion >= 9).
    If you are interested in using stragg() and are wondering where it is in the database, it is a user built aggregate function handrolled by the Inestimable Mr Kyte. You will find the source over at his Ask Tom site.

    Cheers, APC
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    Hi Laurent,
    hi !
    Ur query is really great!!
    thanks ;-)
    But I think his
    requirement is slightly different.
    maybe
    for example, if BBDDDFFAACC is the input string
    then the expected output is BDFAC not ABCDF.

    is there any way to maintain the order?
    use PLSQL
    ...

    well

    ...
    ...

    it is friday
    ...
    ...
    one more just for fun
    ...
    SQL> with alphabet as
      2  ( select 'A' c from dual union all
      3      select 'B' from dual union all
      4      select 'C' from dual union all
      5      select 'D' from dual union all
      6      select 'E' from dual union all
      7      select 'F' from dual union all
      8      select 'G' from dual union all
      9      select 'H' from dual union all
     10      select 'I' from dual union all
     11      select 'J' from dual union all
     12      select 'K' from dual union all
     13      select 'L' from dual union all
     14      select 'M' from dual union all
     15      select 'N' from dual union all
     16      select 'O' from dual union all
     17      select 'P' from dual union all
     18      select 'Q' from dual union all
     19      select 'S' from dual union all
     20      select 'T' from dual union all
     21      select 'U' from dual union all
     22      select 'V' from dual union all
     23      select 'W' from dual union all
     24      select 'X' from dual union all
     25      select 'Y' from dual union all
     26      select 'Z' from dual )
     27  select x,
     28     min(c1.c) keep (dense_rank first order by instr(x,c1.c))||
     29     min(c2.c) keep (dense_rank first order by instr(x,c2.c))||
     30     min(c3.c) keep (dense_rank first order by instr(x,c3.c))||
     31     min(c4.c) keep (dense_rank first order by instr(x,c4.c))||
     32     min(c5.c) keep (dense_rank first order by instr(x,c5.c))||
     33     min(c6.c) keep (dense_rank first order by instr(x,c6.c))||
     34     min(c7.c) keep (dense_rank first order by instr(x,c7.c))||
     35     min(c8.c) keep (dense_rank first order by instr(x,c8.c))||
     36     min(c9.c) keep (dense_rank first order by instr(x,c9.c))||
     37     min(c10.c) keep (dense_rank first order by instr(x,c10.c))||
     38     min(c11.c) keep (dense_rank first order by instr(x,c11.c))||
     39     min(c12.c) keep (dense_rank first order by instr(x,c12.c))||
     40     min(c13.c) keep (dense_rank first order by instr(x,c13.c))||
     41     min(c14.c) keep (dense_rank first order by instr(x,c14.c))||
     42     min(c15.c) keep (dense_rank first order by instr(x,c15.c))||
     43     min(c16.c) keep (dense_rank first order by instr(x,c16.c))||
     44     min(c17.c) keep (dense_rank first order by instr(x,c17.c))||
     45     min(c18.c) keep (dense_rank first order by instr(x,c18.c))||
     46     min(c19.c) keep (dense_rank first order by instr(x,c19.c))||
     47     min(c20.c) keep (dense_rank first order by instr(x,c20.c))||
     48     min(c21.c) keep (dense_rank first order by instr(x,c21.c))||
     49     min(c22.c) keep (dense_rank first order by instr(x,c22.c))||
     50     min(c23.c) keep (dense_rank first order by instr(x,c23.c))||
     51     min(c24.c) keep (dense_rank first order by instr(x,c24.c))||
     52     min(c25.c) keep (dense_rank first order by instr(x,c25.c))||
     53     min(c26.c) keep (dense_rank first order by instr(x,c26.c))
     54  from t left join
     55      alphabet c1 on (instr(t.x,c1.c)>0) left join
     56      alphabet c2 on (instr(t.x,c2.c)>instr(t.x,c1.c)) left join
     57      alphabet c3 on (instr(t.x,c3.c)>instr(t.x,c2.c)) left join
     58      alphabet c4 on (instr(t.x,c4.c)>instr(t.x,c3.c)) left join
     59      alphabet c5 on (instr(t.x,c5.c)>instr(t.x,c4.c)) left join
     60      alphabet c6 on (instr(t.x,c6.c)>instr(t.x,c5.c)) left join
     61      alphabet c7 on (instr(t.x,c7.c)>instr(t.x,c6.c)) left join
     62      alphabet c8 on (instr(t.x,c8.c)>instr(t.x,c7.c)) left join
     63      alphabet c9 on (instr(t.x,c9.c)>instr(t.x,c8.c)) left join
     64      alphabet c10 on (instr(t.x,c10.c)>instr(t.x,c9.c)) left join
     65      alphabet c11 on (instr(t.x,c11.c)>instr(t.x,c10.c)) left join
     66      alphabet c12 on (instr(t.x,c12.c)>instr(t.x,c11.c)) left join
     67      alphabet c13 on (instr(t.x,c13.c)>instr(t.x,c12.c)) left join
     68      alphabet c14 on (instr(t.x,c14.c)>instr(t.x,c13.c)) left join
     69      alphabet c15 on (instr(t.x,c15.c)>instr(t.x,c14.c)) left join
     70      alphabet c16 on (instr(t.x,c16.c)>instr(t.x,c15.c)) left join
     71      alphabet c17 on (instr(t.x,c17.c)>instr(t.x,c16.c)) left join
     72      alphabet c18 on (instr(t.x,c18.c)>instr(t.x,c17.c)) left join
     73      alphabet c19 on (instr(t.x,c19.c)>instr(t.x,c18.c)) left join
     74      alphabet c20 on (instr(t.x,c20.c)>instr(t.x,c19.c)) left join
     75      alphabet c21 on (instr(t.x,c21.c)>instr(t.x,c20.c)) left join
     76      alphabet c22 on (instr(t.x,c22.c)>instr(t.x,c21.c)) left join
     77      alphabet c23 on (instr(t.x,c23.c)>instr(t.x,c22.c)) left join
     78      alphabet c24 on (instr(t.x,c24.c)>instr(t.x,c23.c)) left join
     79      alphabet c25 on (instr(t.x,c25.c)>instr(t.x,c24.c)) left join
     80      alphabet c26 on (instr(t.x,c26.c)>instr(t.x,c25.c))
     81  group by x;
    X                MIN(C1.C)KEEP(DENSE_RANKFI
    ---------------- --------------------------
    AAABBCCDDEEA     ABCDE
    BBDDDFFAACC      BDFAC
    DHSGHDADSFDF     DHSGAF
  • 94799
    94799 Member Posts: 2,208
    STRAGG does not preserve the order of the rows. It is possible to create an aggregate that does and that is discussed at the same link.

    Probably you could do this in straight SQL but such a requirement tends to lead to rather ugly SQL, for example...
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> SELECT dcode, MAX (REPLACE (SYS_CONNECT_BY_PATH ( 
      2            column_value, '/'), '/')) dcode_set
      3  FROM  (SELECT dcode, column_value, 
      4                ROW_NUMBER () OVER ( 
      5                   PARTITION BY dcode ORDER BY r) r1 
      6         FROM  (SELECT dcode, ROWNUM r, column_value 
      7                FROM  (SELECT dcode, 
      8                             (SELECT SET (CAST (COLLECT ( 
      9                                        SUBSTR (dcode, LEVEL, 1)) AS KU$_VCNT)) 
     10                              FROM   dual 
     11                              CONNECT BY LEVEL <= LENGTH (dcode)) t 
     12                       FROM   dt_test_dump), TABLE (t))) 
     13  START WITH r1  = 1 
     14  CONNECT BY dcode = PRIOR dcode AND r1 = PRIOR r1 + 1 
     15  GROUP BY dcode;
    
    DCODE           DCODE_SET
    --------------- --------------------
    DHSGHDADSFDF    DHSGAF
    AAABBCCDDEEA    ABCDE
    BBDDDFFAACC     BDFAC
    
    SQL> 
    The function approach is likely to make your final SQL much simpler although it may be rather less efficient depending on quite how complex the SQL alternative is.

    The 10g SET command may come in handy here since it appears to de-duplicate without sorting.
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> CREATE OR REPLACE TYPE varchar2_table AS 
      2     TABLE OF VARCHAR2 (4000);
      3  /
    
    Type created.
    
    SQL> CREATE OR REPLACE FUNCTION chr_to_table (
      2     p_chr IN VARCHAR2)
      3     RETURN VARCHAR2_TABLE PIPELINED
      4  IS
      5  BEGIN
      6     FOR i IN 1 .. LENGTH (p_chr) LOOP
      7        PIPE ROW (SUBSTR (p_chr, i, 1));
      8     END LOOP;
      9     RETURN;
     10  END chr_to_table;
     11  /
    
    Function created.
    
    SQL> CREATE OR REPLACE FUNCTION table_to_chr (
      2     p_table IN VARCHAR2_TABLE) 
      3     RETURN VARCHAR2
      4  IS
      5     v_chr VARCHAR2 (4000);
      6  BEGIN
      7     FOR i IN 1 .. p_table.COUNT LOOP
      8        v_chr := v_chr || p_table (i);
      9     END LOOP;
     10     RETURN v_chr;
     11  END table_to_chr;
     12  /
    
    Function created.
    
    SQL> SELECT dcode, table_to_chr (
      2            SET (chr_to_table (dcode))) dcode_set
      3  FROM   dt_test_dump;
    
    DCODE           DCODE_SET
    --------------- --------------------
    AAABBCCDDEEA    ABCDE
    DHSGHDADSFDF    DHSGAF
    BBDDDFFAACC     BDFAC
    
    SQL> 
  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,223 Employee
    Alphabet:

    select chr(level+64) from dual
    connect by level <= 26;
  • Vadim Tropashko-Oracle
    Vadim Tropashko-Oracle Posts: 1,223 Employee
    Laurent's solution in a condenced form:

    <code>
    with alphabet as (
    select chr(level+64) c from dual
    connect by level <= 26
    ), strs as (
    select x, replace(sys_connect_by_path(substr(x,instr(x,c),1),' '),' ','') s
    from chars, alphabet
    connect by instr(x,c)>instr(x, prior c)
    and x = prior x
    start with instr(x,c)>0
    ) select * from strs i
    where length(s)=(select max(length(s)) from strs ii where i.x=ii.x);

    X S
    AAABBCCDDEEA ABCDE
    BBDDDFFAACC BDFAC
    DHSGHDADSFDF DHSGAF
    </code>
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    edited January 2006
    we can use recursive PL/SQL too
    create or replace function f(v varchar2) return varchar2 is
    begin
        if (v is null) then return null;
        else return substr(v,1,1)||f(replace(substr(v,2),substr(v,1,1)));
        end if;
    end;
    /
    
    SQL> select x,f(x) from t
    X            F(X)
    ------------ -------
    AAABBCCDDEEA ABCDE
    BBDDDFFAACC  BDFAC
    DHSGHDADSFDF DHSGAF
    Message was edited by:
    Laurent Schneider
    copy-paste...

    Message was edited by:
    Laurent Schneider
    better than friday...
  • 121011
    121011 Member Posts: 566
    create or replace function f(v varchar2) return varchar2 is
    begin
    if (v is null) then return null;
    else return substr(v,1,1)||f(replace(v,substr(v,1,1), NULL));
    end if;
    end;
    /

    :))
  • 472187
    472187 Member Posts: 87
    SELECT * FROM (
    SELECT REPLACE(sys_connect_by_path(str,'~'),'~','') str1
    FROM (
    SELECT str,ROWNUM f,ROWNUM+1 l
    FROM (
    SELECT ROWNUM, SUBSTR('DHSGHDADSFDF',ROWNUM,1) str,rank() over (PARTITION BY SUBSTR('DHSGHDADSFDF',ROWNUM,1) ORDER BY ROWNUM) rk FROM user_objects
    WHERE ROWNUM <= LENGTH('DHSGHDADSFDF')
    ORDER BY ROWNUM
    )WHERE rk=1)
    START WITH f=1
    CONNECT BY PRIOR l=f
    ORDER BY str1 DESC
    )
    WHERE ROWNUM <2
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited December 2007
    create table dt_test_dump(dcode varchar2(12));
    insert into dt_test_dump values('AAABBCCDDEEA');
    insert into dt_test_dump values('DHSGHDADSFDF');
    insert into dt_test_dump values('BBDDDFFAACC');
    commit;
    col dcode for a12
    col dcode_no_repeats for a15
    select dcode,replace(max(sys_connect_by_path(str,',')),',') as dcode_no_repeats
    from (select dcode,str,Row_Number() over(partition by dcode order by Counter) as Rank
    from (select a.dcode,b.Counter,
    substr(a.dcode,b.Counter,1) as str,
    min(b.Counter) over(partition by a.dcode,substr(a.dcode,b.Counter,1)) as minCounter
    from dt_test_dump a,(select RowNum as Counter from all_catalog) b
    where b.Counter <= Length(a.dcode))
    where Counter = minCounter)
    start with Rank = 1
    connect by prior dcode = dcode
    and prior Rank = Rank-1
    group by dcode;
  • Maestro_Vineet
    Maestro_Vineet Member Posts: 135 Bronze Badge
    Thanks to all the Pundits.

    But i must say that one of the most optimal solution was provided by Ashis.
    Hats Off 2 u Mate.
    Good Job.
    c u Mates
    Cheerz
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Not sure when was the last follow up here, but anyway, there are still other options here:
    SQL> with t as (
     select 'AAABBCCDDEEA' str from dual union all
     select 'BBDDDFFAACC'      from dual union all
     select 'DHSGHDADSFDF'     from dual
    )
    select str,
           dbms_xmlgen.getxmltype ('select set(sys.dbms_debug_vc2coll(' || rtrim (regexp_replace (str, '(.)', '''\1'','), ',') || ')) from dual').extract ('//text()').getstringval() new_str
      from t
    /
    STR          NEW_STR                       
    ------------ ------------------------------
    AAABBCCDDEEA ABCDE                         
    BBDDDFFAACC  BDFAC                         
    DHSGHDADSFDF DHSGAF                        
    
    3 rows selected.
  • Billy Verreynne
    Billy Verreynne Member Posts: 28,269 Red Diamond
    My try. :-)

    SQL> -- the approach - pivot a single varchar2 column into a rows and then
    SQL> -- select from these, grouping similar characters and ordering it by the
    SQL> -- character's first occurance
    SQL> with DATASET as(
    2 select
    3 'AAABBCCDDEEA' as C,
    4 level as I,
    5 SUBSTR( 'AAABBCCDDEEA', level, 1 ) as CI
    6 from dual
    7 connect by level <= LENGTH( 'AAABBCCDDEEA' )
    8 )
    9 select
    10 ci
    11 from dataset
    12 group by
    13 ci
    14 order by
    15 MIN(i)
    16 /

    CI
    ---
    A
    B
    C
    D
    E

    SQL> -- make a function using this approach that "un-pivots" the results back into
    SQL> -- a varchar2 data type
    SQL> create or replace function TediousQuery( colValue varchar2 ) return varchar2 is
    2 type TChars is table of varchar2(1);
    3 charArray TChars;
    4 str varchar2(4000);
    5 begin
    6 with DATASET as(
    7 select
    8 colValue as C,
    9 level as I,
    10 SUBSTR( colValue, level, 1 ) as CI
    11 from dual
    12 connect by level <= LENGTH( colValue )
    13 )
    14 select
    15 ci bulk collect into charArray
    16 from dataset
    17 group by
    18 ci
    19 order by
    20 MIN(i);
    21
    22 for i in 1..charArray.Count
    23 loop
    24 str := str || charArray(i);
    25 end loop;
    26
    27 return( str );
    28 end;
    29 /

    Function created.

    SQL> -- example:
    SQL> with DATASET as (
    2 select 'AAABBCCDDEEA' as COL1 from dual
    3 union all
    4 select 'BBDDDFFAACC' from dual
    5 union all
    6 select 'DHSGHDADSFDF' from dual
    7 )
    8 select
    9 col1,
    10 TediousQuery( col1 ) as TEDIOUS_RESULT
    11 from dataset
    12 /

    COL1 TEDIOUS_RESULT
    ------------------------------------ --------------
    AAABBCCDDEEA ABCDE
    BBDDDFFAACC BDFAC
    DHSGHDADSFDF DHSGAF

    SQL>
  • Bolev
    Bolev Member Posts: 566
    Hi all !
    Looking for this exhilarating thread found your solution the most efficient,
    when string needs to be ordered
    I would use the following template for my own
    DECLARE
    str VARCHAR2 (100);
    new_str VARCHAR2 (100);
    BEGIN
    str := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    --str may be populated using a function (like get_alphabet)

    FOR rec IN (SELECT 'ZZAAABBCCDD' dmp
    FROM DUAL
    UNION
    SELECT 'BBDDDFFAACC' dmp
    FROM DUAL
    UNION
    SELECT 'DHSGHDADSFDF' dmp
    FROM DUAL)
    LOOP
    new_str := --That is all we need !!
    TRIM (TRANSLATE (str, TRANSLATE (str, rec.dmp, ' '), ' ') );
    DBMS_OUTPUT.PUT_LINE (new_str);

    END LOOP;
    END;

    --
    ---------
    ABCDF
    ADFGHS
    ABCDEZ
  • Bolev
    Bolev Member Posts: 566
    edited January 2008
    One more possibility with bulk collect:
    DROP TABLE t;
    CREATE TABLE t (dmp VARCHAR2(100));
    INSERT INTO t
    VALUES ('ZZAAABBCCDD');
    INSERT INTO t
    VALUES ('BGTARRGTYRR');
    COMMIT ;

    DECLARE
    str VARCHAR2 (100);
    TYPE vr_tbl_type IS TABLE OF VARCHAR2 (100);
    atbl vr_tbl_type;
    BEGIN
    str := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

    SELECT REPLACE (TRANSLATE (str, TRANSLATE (str, t.dmp, ' '), ' '), ' ')
    BULK COLLECT INTO atbl
    FROM t;
    DBMS_OUTPUT.put_line (atbl.COUNT);
    DBMS_OUTPUT.put_line (atbl (1));
    DBMS_OUTPUT.put_line (atbl (2));
    END;

    ----
    ABCDZ
    ABGRTY

    Message was edited by:
    Bolev
  • 572471
    572471 Member Posts: 984 Green Ribbon
    Sure the OP found the needed solution as he posted this thread a year ago.
    But just to store it in one place - I post a solution with Model here.

    It was my first post in the blog: http://volder-notes.blogspot.com/2007/10/removing-duplicate-elements-from-string.html
This discussion has been closed.