SQLパズル：文字列中の重複する文字を削除する

文字列中の重複する文字を削除する - ミックのブログ

もろに手続き型で解いてみた。

with recursive temp (word_key, idx, word_txt) as
(
  select word_key, 1 as idx, cast(substring(word_txt from 1 for 1) as varchar(25))  as word_txt
  from WordList
  union all
  select temp.word_key, temp.idx + 1 as idx,
    case
      when position(substring(w.word_txt from temp.idx + 1 for 1) in w.word_txt)  =  temp.idx+1
        then temp.word_txt || substring(w.word_txt from temp.idx + 1 for 1)
      else temp.word_txt end as word_txt
  from WordList w, temp
  where w.word_key = temp.word_key
    and CHARACTER_LENGTH(w.word_txt) > temp.idx
)
select x.word_key, x.word_txt
from temp x join (select word_key, max(idx) as max_idx from temp group by word_key) as y
  on x.word_key = y.word_key and x.idx = y.max_idx
order by x.word_key

解けたけど、エレガントじゃなさすぎる…。

2008-08-16T01:00:00+9:00追記
MAX使わなくて良い様にフラグにしてみた。

WITH RECURSIVE temp (word_key, idx, word_txt, end_flag) AS
(
  SELECT
    word_key,
    1 AS idx,
    CAST(SUBSTRING(word_txt FROM 1 FOR 1) AS VARCHAR(25)) AS word_txt,
    0 AS end_flag
  FROM
    WordList
  UNION ALL
  SELECT
    temp.word_key,
    temp.idx + 1 AS idx,
    CASE
      WHEN POSITION(SUBSTRING(w.word_txt FROM temp.idx + 1 FOR 1) IN w.word_txt) = temp.idx + 1
        THEN temp.word_txt || SUBSTRING(w.word_txt FROM temp.idx + 1 FOR 1)
      ELSE temp.word_txt END AS word_txt,
    CASE temp.idx + 1
      WHEN CHARACTER_LENGTH(w.word_txt) THEN 1
      ELSE 0 END AS end_flag
  FROM
    WordList w,
    temp
  WHERE
    w.word_key = temp.word_key
    AND CHARACTER_LENGTH(w.word_txt) > temp.idx
)
SELECT
  word_key,
  word_txt
FROM
  temp
WHERE
  end_flag = 1
ORDER BY
  word_key