OraQA

The following two SQL patterns can be used to remove duplicate characters from a string. The original order of the characters will also be retained.

Create table t5 as
SELECT 'SUNDSNUDADY' str FROM dual
union all
SELECT 'MONONDAAY' str FROM dual
union all
SELECT 'FRRIIDFAAYY' str FROM dual;

——————————————-10G Solution one———————————————————–

SELECT str, fin_str
FROM (SELECT ch, str, fin_str
      FROM (SELECT str,SUBSTR(str,LEVEL,1) ch, ROWNUM num
            FROM t5  
            CONNECT BY PRIOR str = str
            AND LEVEL <= LENGTH(str)
            AND PRIOR DBMS_RANDOM.STRING ('p',10) IS NOT NULL
     )
MODEL
PARTITION BY (str)
DIMENSION BY (row_number() over (PARTITION BY str ORDER BY num) rn)
MEASURES
(CAST(NULL AS VARCHAR2(3999)) tmp_str,ch,CAST(NULL AS VARCHAR2(3999)) fin_str)
RULES
(tmp_str[ANY] ORDER BY rn  =
 CASE WHEN ch[cv()-1] IS NULL
      THEN ch[cv()]
 ELSE CASE WHEN instr(tmp_str[cv()-1], ch[cv()])<1
                THEN tmp_str[cv()-1]||ch[cv()]
      ELSE tmp_str[cv()-1]
       END
 END,
 fin_str[ANY] ORDER BY rn = CASE WHEN ch[cv()+1] IS NULL
                                 THEN tmp_str[cv()]
              END         
 )
)
WHERE fin_str IS NOT NULL
ORDER BY fin_str ;

STR              FIN_STR
------------     ----------
FRRIIDFAAYY      FRIDAY
MONONDAAY        MONDAY
SUNDSNUDADY      SUNDAY

————————Solution two———————————–

SELECT str, path FROM
(SELECT path, str, length(path) len, 
        MAX(length(path)) OVER (PARTITION BY str) max_len
 FROM
 (SELECT str, REPLACE(sys_connect_by_path (ch,' '),' ') path
    FROM (SELECT str, ch, row_number() OVER
                          (PARTITION BY str ORDER BY min) rn
           FROM (SELECT str, ch, min(num) min 
       FROM (SELECT str,SUBSTR(str,LEVEL,1) ch, rownum num
                       FROM t5  
                       CONNECT BY PRIOR str = str
                      AND LEVEL <= LENGTH(str)
                      AND PRIOR DBMS_RANDOM.STRING ('p',10) IS NOT NULL
       )
                  GROUP BY  str, ch
                 )
          )
   CONNECT BY PRIOR rn = rn -1
   AND PRIOR str = str
  )
)
WHERE len = max_len
ORDER BY path;

STR              FIN_STR
------------     ----------
FRRIIDFAAYY      FRIDAY
MONONDAAY        MONDAY
SUNDSNUDADY      SUNDAY

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.