英文:
Remove duplicate with start and end character in sql
问题
I have a string and need to remove duplicate by select statement in ORACLE SQL.
e.g: 'apple-HenryHenry(Male)-SunnySunny(Female)-apple'
I want to resulting output would be: 'apple-Henry(Male)-Sunny(Female)-apple'
Everyone help me
SELECT REGEXP_REPLACE('apple-HenryHenry(Male)-SunnySunny(Female)-apple', '([[:alnum:]]+)()+', '') AS result
FROM dual;
英文:
I have a string and need to remove duplicate by select statement in ORACLE SQL.
e.g: 'apple-HenryHenry(Male)-SunnySunny(Female)-apple'
I want to resulting output would be: 'apple-Henry(Male)-Sunny(Female)-apple'
Everyone help me
SELECT REGEXP_REPLACE('apple-HenryHenry(Male)-SunnySunny(Female)-apple', '([[:alnum:]]+)(\1)+', '\1') AS result
FROM dual;
答案1
得分: 1
你的查询大部分正确，但它会从单词中移除重复的字母和重复的部分（比如 banana -> bana）。
你可以使用类似于下面这样的代码：
SELECT REGEXP_REPLACE('apple-HenryHenry(Male)-SunnySunny(Female)-apple', '(^|\\W)(\\w+)\+(\\W|$)', '\\\') AS result
FROM dual;
在这里，(^|\\W)(\\w+)\\2+(\\W|$) 匹配由两个或更多相等部分组成的，被非单词符号或字符串边界包围的单词。
这并不是完美的，因为它会修改 haha -> ha。如果可以保证重复单词的两个部分都以大写字母开头，你可以使用 (^|\\W)([A-Z]\\w*)\\2+(\\W|$) 代替。
SELECT REGEXP_REPLACE('bananabanana-HenryHenry(Male)-SunnySunny(Female)-apple', '(^|\\W)([A-Z]\\w*)\+(\\W|$)', '\\\') AS result
FROM dual;
-- 输出: bananabanana-Henry(Male)-Sunny(Female)-apple
英文:
Your query mostly correct, but it will remove double letters from words, an duplicate parts of words (like banana -> bana).
You can use something like this
SELECT REGEXP_REPLACE('apple-HenryHenry(Male)-SunnySunny(Female)-apple', '(^|\W)(\w+)\2+(\W|$)', '\1\2\3') AS result
FROM dual;
Here (^|\W)(\w+)\2+(\W|$) matches words, surrounded by non word symbols or string boundaries, that consists of two or more equal parts.
It is not ideal, because it will modify haha -> ha. If it is guaranteed that both parts of duplicated word start with upper letter, you can use (^|\W)([A-Z]\w*)\2+(\W|$) instead.
SELECT REGEXP_REPLACE('bananabanana-HenryHenry(Male)-SunnySunny(Female)-apple', '(^|\W)([A-Z]\w*)\2+(\W|$)', '\1\2\3') AS result
FROM dual;
-- Outputs: bananabanana-Henry(Male)-Sunny(Female)-apple
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论